Lookup Rate From Table

[John_Savidge]

Hi,

I need to lookup a value from shipping rate tables based on the weight value from another table.

I have two tables I need to look up data from, an example of one is below, The other is the same structure but different data.

This is my source table containing my weight values.

I need to select a value from the 2nd column on the above table, when the weight in my source table is between two row values.

For example, if source weight is 7.75kg, it should return 53.3 for the rate.

Hope someone can point me in the right direction 😁

Thanks

john

[kuovonne · Accepted solution]

You can do this with an automation.

Your rates table needs to have an additional field: {Max Weight}. I recommend moving your current {Weight} field to a {Min Weight} field and converting the primary field to a formula field that concatenates the {Min Weight} and {Max Weight}.

In your automation, have a "Find Records" action where you find a record in the rate table, where the {Min Weight} is less than or equal to the triggering record's {Weight}, and the {Max Weight} is greater than the triggering record's {Weight}.

Then have conditional actions based on zone you want to use to update the {Base Shipping Rate}. This is assuming that the triggering record knows its zone. For example, if the triggering record's {Zone} is "Zone 2", then use an "Update Record" action to copy the rate for "Zone 2" from the rates table to the {Base Shipping Rate} of the triggering record. 

[John_Savidge]

Thank you @kuovonne I will give this a try.