GetRecords Formula Help
Looking for assistance to help figure out why my formula isn't working as expected.
Setup:
Rental Rates table: used to capture the Daily, Weekly, and Monthly rental amounts for Assets. There is a lookup field to select the related asset (FID 12). There is a number field to enter the Rental Year Coverage (FID 14). There is a currency field to capture the Rental Rate (FID 8)
Rentals table: used to capture the individual rentals for specific assets. There is a lookup field to select the related asset (FID 6). There is a number field to enter the Rental Year (FID 20).
In my Rentals table I have a formula-numeric field and need to find the Rental Rate (Rental Rate table FID 8) IF the related asset (Rental table FID 6) equals the related asset (Rental Rates table FID 12) AND the Rental Year (Rental table FID 20) equals Rental Year Coverage (Rental Rates table FID 14).
I found various posts and put together the following formula:
GetFieldValues(GetRecords("{12.EX.'"&[Related Asset]&" '}AND{14.EX.'"&[Rental Year]&"'}",[_DBID_RENTAL_RATES]), 8)
The field won't save as it is saying that it is Expecting number but found textlist. I tried adding "ToNumber" prefix, but the error just changes to Expecting text/bool/number but found textlist.
I have confirmed that each of the fields being compared are of the same type. What am I missing?
Thanks
Got it to work using:
SumValues(GetRecords("{13.EX.'"&[Asset Tag ID]&"'}AND{14.EX.'"&[Rental Year]&"'}",[_DBID_RENTAL_RATES]),8)
Removed the GetFieldValues parameter and replaced with SumValues. That at least got rid of the error and returned a value. The value was wrong-ish. It was retrieving values but not for the intended record, had to change the comparison fields, once I did it is now pulling the correct values.
