Lookup item in another table based on date selection in target table

Ask for Help
1

Hi All

I am creating an expense tracker / mileage logger and have one table to define reimbursement rates which change periodically based on fuel price fluctuations. So each new rate is defined in the “rates” table with a fixed start and end date.

Now I need to have the valid rate applied when a user logs a trip based on the date they nominate as the date of travel in the mileage log table.

The periods of validity for each published rate is not set and can vary from a few days to 6 months or even longer.

I am using glide tables only.

Any suggestions or pointers to existing show cases / examples would be appreciated.
(I tried to create a javascript column to generate a list of integers between the start and end dates (converted to integer values) in the rates table, that I would then use in an array which could be referenced in a relation, but struggling to get the javascript column to work.)

Appreciate guidance here.
Many thanks
Mark

2

You’ll need to use a custom form for logging entries. So each of your form inputs will be writing to user specific columns.

  • when a user selects a date, use a single value column to apply that to all rows in your rates table.
  • add an if-then-else column to your rates table:
    — if start date is after single value date, then null (leave empty)
    — if end date is before single value date, then null
    — else rate
  • the result of the above should be just one row in your rates table with a value in that column (unless you have overlapping dates)
  • now you can use a rollup->max on the if-then-else column to select the applicable rate
1 Like