Re: Return a value from the newest linked record

1407 0
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi, I’m so sorry to be repeating what other people have already asked, I have tried a few of the solutions previously suggested, but I am such a novice, I’m obviously doing something wrong. I’m hoping that if I give my specific examples someone really clever and very patient can give me a step by step idiots guide please. This is the simplified version:

Table A = purchase orders to buy various types of material
Table B = batches of material booked into stock (from the purchase orders)
Table C = customer orders to sell the material

These tables are linked so that every time a purchase order is completed (Table A) the material gets booked into stock (Table B) and the quantities increased accordingly, then every time a customer order is completed (Table C) it deducts the corresponding quantity from stock - so far so good.

In Table C there is a link to Table B check the stock levels and I want to also at this stage be able to see at a glance the price of the most recent batch purchased without having to go into Table A to find that information.

In Table B there is a lookup field showing the prices from the linked purchase orders in Table A, but they don’t show in reverse order so after a while the most recent values are no longer visible (I understand there used to be an option to reverse the order but it was removed for some reason such a shame).

Anyway, I’ve seen other answers saying to use this type of rollup function: IF(MAX(values) = {field name}, {field name}) and I managed to get as far as creating a rollup field on the material stock table B to show the date of most recent linked purchase order, but from there on it kept going horribly wrong. I either got error messages or simply no value returned.

Many thanks, Gill

7 Replies 7

I demonstrate how to do the technique that you’re looking for in this episode of the BuiltOnAir podcast:

Hi, thankyou so much for replying, it’s late here now and my brain is drained, but I’m looking forward to watching this tomorrow, fingers crossed. Cheers, Gill

Hi Scott, Thanks for this… I too am trying to learn latest record functions… Can’t watch the whole show, subscribed for future, but would you be willing to provide a time stamp to help narrow down where you speak about it specifically?

Hi Graham, I just started to watch the video, it’s got the full transcript underneath separated into paragraphs with time stamps so it’s really easy to navigate and find specific things. I’ve just got to the part where they start discussing this topic, it’s at 28 mins in. I’ll update you after I’ve tried it out, fingers crossed.

Thanks Gill!! Will have another look!

Hi Graham, I got my end result to work, but not exactly how they explained in this video although that would work as well. But after I watched this video I then went and re-read several previous posts on this and the penny dropped about having to go back and forth between the tables with rollups and lookups. How are you getting on?

6 - Interface Innovator
6 - Interface Innovator

Havent had much aIt’s been a week. Hoping to get a chance to watch it this week.