Please can beg for some FM18 help! I have 2 tables TPTable::date that is linked to HLTable:HL::date. I need to automate the look up of 2000 TPTable records to the closest match HLDate (80 records). I know there must be a way, but it has alluded me thus far! Thanks

Greetings - need some more context what is unique in each table that could be used as a match to lookup data?

you could use multiple predicates in the relationship or concatenate several fields together to make a unique key to lookup data. 

 

Thanks for prompt reply. I have a unique ID for each table. __TPRecordID and __HLRecordID, does that help?

If you define a relationship between the two tables matching on the date field, you can use a calculation in the first table to find the closest date in the second table: use the LookupNext() function to find the nearest date before the given date; use the same function to find the nearest date after the given date; compare the differences and select the date with the smaller one.

 

Let me try that again. I had a field cLookup_higher in my TPTable that auto calculates:

LookIpNext(HlTable::HLdate;Higher). The relationship between HLTable and TPTable is HLdate < date AND HLdate >= date. Unfortunately this did not yield a result... HLdate = date didn’t work either. 

I am afraid I am not able to follow your description. Does the attached demo file do what you expect?

 

ClosestDate.fmp12

Thank you thank you thank you! It does. It was my relationships that was at fault. 😊

Hi "Comment" - sorry to bother again. Closest Date not working after I changed my fields . I have replicated your relationships: closestdate = date (in my case cLookUp = HLDate) and it always returns original HLDate. Simplified I have three relationships between tables:
HLTable - TPTable -> HLID_pk = HLID_fk  this is my link to create TPTable from HLTable where I add various dates to my HLDate to derive my new cTPDate. To recap I have the original date from Excel import, HLDate, I bring HLDate to TPTable using a script and the add various dates to HLDate in TPTable to give me my cTPDate

HLTable_lookup - TPTable -> HLDate = cLookup

HLTable_date - TPTable -> HLDate = HLDate (replicating your model), it just returns my original HLDate. Also tried HLDate = cTPDate, which doesn't work same thing - not the closest match.

cLookUp is strictly your calculation whether I reference to HLTable::HLDate  it just returns my original HLDate. If I reference to HLTable_date::HLDate,  it still returns my original HLDate- not the closest match. If I reference to HLTable_lookup::HLDate or  it just crashes.

Any ideas, thanks

I am afraid I couldn't debug your file without seeing it even if I wanted to... 🙃

 

 

Thanks anyway - I shall rebuild the tables and see what went wrong.