Perform Lookup Based On Calculation and Return Value

[Craig_Watson]

Hi,

I have two tables … | Players | Membership Fees |

Membership fees are based on your age at the beginning of a season and the season start date is stored in the Membership Fees Table.

From the Players table I enter the Name and DOB, id like to lookup the relevent membership fee by checking if the players age at the start of the season fits one of the categories set out in the membership fees table.

Image below might help explain, I want to check the age at the start of the season is less than 16 then its a junior, between 17 and 65 its senior and 66 + its honorary.

I also want to show the fee payable in the Players table.

All help appreciated…

[David_Skinner]

The simplest way is probably to have Linked Record fields in the Players table for each membership level. Call the fields Junior Membership, Senior Membership and Honorary or Life Membership. Then link the relevant record in each field fore every player in the Players table. You’ll then need to have three Look Up fields (Junior Membership Fee, Senior Membership Fee and Honorary or Life Membership Fee) in the Players table. These will look up the Membership Fee field. You’ll also need to get the Season Start Date into the Players table via a Look Up field.

You can then use a Formula field to calculate the fee required, something like this:

IF( DATETIME_DIFF( {DOB}, {Season Start Date}, ‘years’ ) < 17, {Junior Membership Fee}, IF (DATETIME_DIFF( {DOB}, {Season Start Date}, ‘years’ ) > 65, {Honorary or Life Membership Fee}, {Senior Membership Fee} ) )