How to Lookup and retern spacific record from Diff Table to the current Table ?

Topic Labels: Automations Formulas
269 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello , thanks for taking time for giving a hand  on this 
Table 1 ( Currency ) for Monthly Foreign Currency Ex Rates
USD Nov 2023
SAR Nov 2023
EGY Nov 2023 

Table 2 ( Budget Line ) which contain the monthly budget line in different Monther 
Table 2 will have the same format as table 1 
USD Nov 2023
SAR Nov 2023
EGY Nov 2023 

in some of its records 
My question 
can i find a way that match the budget line that occure in Nov 2023 With the monthly matched exchange rate in currency record ? 

  Screenshot 2023-11-30 000014.pngScreenshot 2023-11-29 235957.png

1 Reply 1

Two options come to mind:

Instead of putting your currencies and monthly rates in one table, have a Currencies table and a Rates table. The Currencies table has one record for each Currency you buy/sell in; the Rates table has one record per currency per month (or whatever frequency the rates change). When you buy or sell something, you link the rates record and you can look up the corresponding rate for that purchase or sale. 

Use an automation that is triggered by checking a box in the Budget Line record, that finds the record in your existing Currency table where the BT Line Month entry matches the Currency Month entry, and the update the Budget Line record with the exchange rate.