I have a table entitled work codes. Within the table there are multiple codes and descriptions, how do I attach a formula to one code within the table?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

each code is an expense code, and for certain expenses, for example kilometres, it is necessary to multiply the amount entered by 0.45. (gas)

is there a way to set up a formula so it only effects one code within the table and not all of them?

A look up field from the table Expenses, where you choose the code and enter other information, looks up info from the Codes field in the Expense code table.

How do I create a formula so when km is chosen in the expense code look up field, it then autimatically knows to multiple the amount of kilometres entered by 0.45?

Photo of Hannah


  • 0 Points

Posted 4 years ago

  • 0
  • 1
I suggest that you rename field with a suffix of (lookup).  For example [Amount (lookup)]

Then have a new field be called [Amount] with a formula of

IF([code]="kms",[Amount (lookup)] * 0.45,[Amount (lookup)])

I would also suggest that you have a table to hold the current gas rate and bring it down as a lookup and snapshot it.  That way when the per km reimbursement rate changes form time to time, your historical records will not suddenly change.

To set up that table, make a table called KM Reimbursement rate and make 1 record in it.  It will be record ID of 1.  In your details table make a field called [Link to Km rate] with a formula numeric formula of 1.  Make a relationship to the KM table based on that field and lookup the rate and then make a snapshot field.  

Here is some help on snapshots