I have a second table called Sales Price that is a list of daily prices of the commodities.
2019-03-25 product1price product2price etc.
I want to create a field in the Pickup object that pulls the prices for the day that the pickup took place, so each pickup record shows the prices that were valid on that date.
I thought I could do it with a formula field, but it seems I cannot connect to the Sales Price table from the Pickups table.
There will be lots of Pickup records on one date, but only one Sales Price record per day.
6 answers
I was kinda hoping that since this is basically just an excel VLOOKUP there would be an easier solution.
Here is what I came up with:
- I'll make a lookup field on the Pickup objects that connects to the right Sales Price record based on the date.
- Make a process automation that fires for new or edited Pickup records and connects the lookup field to the right Sales Price
- make new formula fields in the Pickup to grab the fields from the linked Sales Price record.
For me, this is totally easier than learning flows or apex.
I hear you about the data model seeming wonky. Since we are basically selling a bulk commodity by the pound instead of by unit, this structure works.
