I have two questions. (I hope I can explain this clearly)
1 I’ve already created a table. This tables show our balance in U.S. dollars. It shows my deposits and withdrawals. The deposits are when I buy U.S. dollars from Canadian dollars. (We buy when the exchange rate is favorable and/or when we absolutely need to buy in order to pay a supplier) & withdrawals, with U.S. rate (US money paid to our suppliers - right now I need to calculate manually the U.S. rate based on money that was bought). Therefore,. the exchange rate when paying our supplier depends on how much it cost us to buy the U.S. dollars.
First question: Is there a way for Filemaker to calculate the exchange rate based on what it cost us the buy the U.S. dollars (balance remaining) Ex:
Bought $47072,11 @ 1,2604 & bought $104927,89 @ 1,2937, therefore the rate to pay our supplier $152,000 is 1,2834.
Since the sBalance field shows me our balance in the bank, I don’t really know how much we paid for this remaining balance.
2 I also need to show on a monthly and/or weekly basis the amount of money that needs to be bought based on upcoming payments (orders placed, but not paid yet). When paid, they will be moved to the table that I have created above. What is the best way to go about this? Create another table, or create a field in the above table (PAID / UNPAID – and redo my calculations so the amounts will not be calculated in the balance, I’m not sure how to do this).
I would also want to easily create reports showing the totals owed to each supplier, etc.