Skip to main content

I’m wracking my brain trying to figure out how I can make this work.



I have an Items table.


I have a Locations table.


I have a Location Records table that joins the two.



An Item has many Location Records, and belongs to a Location through its most recent Location Record, and it has a history of all its past Locations through the sequence of all its Location Records.



In the Items table, I can Rollup Location Records–>{Date}–>MAX(values) to pull the {Date} out of the most recent Location Record. But how can I pull the {Location} out of the most recent Location Record, so that I can display an Item’s {Current Location}?



I can Rollup Location Records–>{Location}–>ARRAYJOIN(values). I was hoping the {Location} value from the most recently added Location Record would be the first in the array – then I could use:



LEFT(


   {Location Rollup},


   SEARCH(


      ",",


      {Location Rollup}


   ) -1


)


to parse out the first value from the array.



But, of course, this isn’t the case – rather, the values in the Rollup array are ordered from oldest to most recent. So I can try to extract the last value from that array, but now I’ve lost the ability to easily find where to parse from, because I have no way to find the last comma in an array, rather than the first comma in an array. UGH!



Anybody have any solutions/work-arounds for this that don’t bring in a third-party service, and don’t involve an all–>one record link?


@W_Vann_Hall @Julian_Kirkness @Elias_Gomez_Sainz @Alex @Alex_Wolfe @Andre_Zijlstra @Kasra





A new Rollup function



POP(values)


would be really great here!

I’m not 100% sure this will work with your dataset, but in the past I have done this for similar purposes by following the steps outlined in this post:





Basically, you pass the data back and forth between your linked tables to get what you need based on the MAX date. However, given that you need the latest location for all Items, this may require more work on the formula side to calculate this for all records - I’d need to spend a bit more time testing/thinking through this but this was my first guess! Hope it is helpful.

I’m not 100% sure this will work with your dataset, but in the past I have done this for similar purposes by following the steps outlined in this post:





Basically, you pass the data back and forth between your linked tables to get what you need based on the MAX date. However, given that you need the latest location for all Items, this may require more work on the formula side to calculate this for all records - I’d need to spend a bit more time testing/thinking through this but this was my first guess! Hope it is helpful.


That worked great! So much simpler than all the things I was trying too!



Thanks for the pointer, @Alex_Wolfe!

That worked great! So much simpler than all the things I was trying too!



Thanks for the pointer, @Alex_Wolfe!


No problem. I wrestled with this til I found that solution a while back as well. It’s definitely a workaround for what will hopefully become built-in functionality one day (to pull specific record data from a linked field).

That worked great! So much simpler than all the things I was trying too!



Thanks for the pointer, @Alex_Wolfe!


@Jeremy_Oglesby, could you tell how exactly did you manage to do this? I am facing the same challenge.

I built out this formula to find the first element of a array, my array is called products. If there is only one product it will just show the first one.



IF(FIND(",", Products)=0, Products, LEFT(Products, FIND(",", Products)-1))

Reply


Terms & Conditions