How to select a rate from table B to a field in table A, where the value falls within a range

[Daisy_Ramirez]

In my quote form, the user selects a Depot location (zip code) and destination (zip code) which calculates the total mileage (distance). In order to determine the shipping rate to use for calculating shipping costs, the system needs to find the correct rate in the Depot Rates table for the selected Depot ID where the mileage calculated falls between a range of miles. (if calculated mileage falls between the start mileage and end mileage, select the rate).

When I use the expression below, it appears to work correctly when I press the Test option but doesn’t appear on screen. I’ve tried the expression in the initial value of my Shipping Mileage Rate field and app formula and none appear on screen. Is the expression correct?

Shipping Mileage Rate:
ANY(SELECT(Depot Rates[rate],AND([depot]=[_THISROW].[depot],[_THISROW].[vir_mileage]>=[start mileage],[_THISROW].[vir_mileage]<=[end mileage])))

Depot Rates table:

[Suvrutt_Gurjar]

The screenshot shows that the formula is in initial value of the field. If you are testing this formula on existing saved records, then I believe the initial value formula will not show any value in the form.

I believe initial formula will work for a new record being added.

[Daisy_Ramirez]

Thanks Suvrutt, yes the view is a form view for a new record

[Suvrutt_Gurjar]

Thank you. Is [Vir_Mileage] a VC in the table where form is there?

[Daisy_Ramirez]

yes

[Suvrutt_Gurjar]

Do you get any results in expression results pane?

I tested with similar test conditions and it works for new records.

[Daisy_Ramirez]

Suvrutt, thank you. What’s odd is I changed nothing and it works now. Might’ve logged out maybe. Thanks as always for the help:)

[Suvrutt_Gurjar]

Great, nice to know you got it working now.