Skip to main content
Solved

Help - Grouping using formula creates issues

V

Hey,

I am creating a base to understand staff allocation over the various projects - 1 person goes to 1 project only until they are transferred. I had to assign people (names) to specific projects (as in their current projects). I have created a sheet called transfers where there is a FROM project and TO project. Over time, each person will have a number of transfers. So in the staff sheet we see the various TO sites where the person has been transferred to over time. Now to find the most recent transfer and hence their current project I used the solution from this answer: Find last entry

So it does some work in the background and basically finds the most recent entry for the person and shows that in the formula as text

The problem is the grouping. As you see the name name has gone into 3 groups Cummins Pithampur Since they are showing text from the same source, they are required to be in the same group. The logic beats me. Some help please.

Thanks
Vatsal.

Best answer by Justin_Barrett

Dalton11 wrote:

Vastal,

Can you double check that you don’t have an extra space at the very beginning or end of the original field where the Projects Worked field is looking up.

This may be causing the grouping to show differently due to even such a small difference.


Technically, a lookup field is most often returning an array, even when the related info is only coming from a single linked record. When grouping by a lookup field, my gut says that Airtable isn’t grouping by the literal text of the single item in that array, it’s somehow grouping by the array’s identity (or something…just taking a stab). There are two options I can think of to get around this: 1) Make a formula field that converts the lookup’s array into a string, and group by that field, or 2) change your lookup to a rollup that outputs a string. The latter will be cleaner because the formula will be contained in the field’s setup, and you don’t need an extra field.

When setting up the rollup field, use the aggregation formula: ARRAYJOIN(values). Assuming that you only have one linked record returning the current site, the groups should clean themselves up.

View original
Did this topic help you find an answer to your question?

6 replies

D
Forum|alt.badge.img
  • Dalton11
  • Participating Frequently
  • 11 replies
  • August 3, 2020

Hey Vatsal,

I think I’m 80% there in understanding what you’re trying to accomplish.

Would you mind sharing a screenshot of the records you are trying to group, and elaborate on how you want those records specifically grouped?

Thanks!

V
Dalton11 wrote:

Hey Vatsal,

I think I’m 80% there in understanding what you’re trying to accomplish.

Would you mind sharing a screenshot of the records you are trying to group, and elaborate on how you want those records specifically grouped?

Thanks!


Hey Dalton, attaching screenshots…

So 1 image shows the ungrouped way and 2nd shows how it is grouped. The names are exactly the same since they are converted to text from an entry…

Thanks for your help!

D
Forum|alt.badge.img
  • Dalton11
  • Participating Frequently
  • 11 replies
  • August 5, 2020

Vastal,

Can you double check that you don’t have an extra space at the very beginning or end of the original field where the Projects Worked field is looking up.

This may be causing the grouping to show differently due to even such a small difference.

Justin_Barrett
Forum|alt.badge.img+20
Dalton11 wrote:

Vastal,

Can you double check that you don’t have an extra space at the very beginning or end of the original field where the Projects Worked field is looking up.

This may be causing the grouping to show differently due to even such a small difference.


Technically, a lookup field is most often returning an array, even when the related info is only coming from a single linked record. When grouping by a lookup field, my gut says that Airtable isn’t grouping by the literal text of the single item in that array, it’s somehow grouping by the array’s identity (or something…just taking a stab). There are two options I can think of to get around this: 1) Make a formula field that converts the lookup’s array into a string, and group by that field, or 2) change your lookup to a rollup that outputs a string. The latter will be cleaner because the formula will be contained in the field’s setup, and you don’t need an extra field.

When setting up the rollup field, use the aggregation formula: ARRAYJOIN(values). Assuming that you only have one linked record returning the current site, the groups should clean themselves up.

kuovonne
1 person likes this
  • kuovonne
    kuovonne
V
Dalton11 wrote:

Vastal,

Can you double check that you don’t have an extra space at the very beginning or end of the original field where the Projects Worked field is looking up.

This may be causing the grouping to show differently due to even such a small difference.


Thanks for the help - It got solved…

V
Justin_Barrett wrote:

Technically, a lookup field is most often returning an array, even when the related info is only coming from a single linked record. When grouping by a lookup field, my gut says that Airtable isn’t grouping by the literal text of the single item in that array, it’s somehow grouping by the array’s identity (or something…just taking a stab). There are two options I can think of to get around this: 1) Make a formula field that converts the lookup’s array into a string, and group by that field, or 2) change your lookup to a rollup that outputs a string. The latter will be cleaner because the formula will be contained in the field’s setup, and you don’t need an extra field.

When setting up the rollup field, use the aggregation formula: ARRAYJOIN(values). Assuming that you only have one linked record returning the current site, the groups should clean themselves up.


Hey Justin!

Your gut was right. In the rollup it showed 3 blanks along with the visible answer hence its grouping might have been different. (as in it searched and showed the current answer along with blanks)

So i created a new formula to concatenate this field and the new output worked perfectly.

Thanks a ton!

Vatsal

Justin_Barrett
1 person likes this
  • Justin_Barrett
    Justin_Barrett

Reply


Most helpful members this week

Terms & Conditions