Change lookup field based on the year
I need to look up one of two fields depending on the year. The two fields are labeled as follows: ‘2022-HHG-Mileage’ and ‘2023-HHG-Mileage’.
Then, the correct field name needs to be inserted in the if/then statement in the function first(xfind.????).
Using the let y := and the let HHG := I can successfully change the year and return the name of the correct field.
However, in the if/then part, I have not been able to figure out how to get the field name into the function: first(xfind.???) where the question marks should be replaced with the correct field name.
let t := this;
let y := text(year('Load End Date'));
let HHG := y + "-HHG-Mileage";
let xfind := (select Trips)[Origin = t.StartLocation and Destination = t.Destination1 or Origin = t.Destination1 and Destination = t.StartLocation];
if count(xfind) > 0 and 'Empty/Loaded1' = 2 then
LoadedMiles1 := first(xfind.HHG)
else
if count(xfind) > 0 and 'Empty/Loaded1' != 2 then
EmptyMiles1 := first(xfind.HHG)
else
alert("No trips match!")
end
end
By the way, if I put
first(xfind.'2022-HHG-Mileage')
or
first(xfind.'2023-HHG-Mileage')
the correct field is found and the miles are correctly inserted into the correct fields.
Thanks for your help,
Chris
2 replies
-
Sadly, I don’t think you can build a field name in a formula. This is because Ninox does not really track field names by the human readable names we give it. It is like URLs, there is a backend naming scheme that Ninox really uses to keep track of field names.
So a way to solve this could be in the structure of your DB.
When you start creating fields that are differentiated by a year then maybe you should start considering building a new table. In this example it could be called HHG-Milage. It can have three fields, a reference field to the first table, a year number and a mileage number. Then you find related records then find the the correct year and you get the correct mileage.
Content aside
- Status Answered
- 1 yr agoLast active
- 2Replies
- 66Views
-
2
Following