Financial Transaction Type
I'm a novice FMPA12v2 Mac user using a financial database I designed from scratch.
I have a one-to-many relationship between my Journal (aka Transaction) parent table, and the Ledger (aka Transaction LineItem) child table, since a transaction can have many line items, but each line item can have one and only one transaction (i.e., one date, one contact name, one memo, one reference number, etc.). Each Transaction can have one and only one transaction type (i.e., Sales, Cash Receipt, Purchase, Cash Payment, and General) but each transaction type can have many transactions. Each transaction can have one and only one Contact (i.e., a customer or vendor name), but each Contact can have many Transactions. Each Transaction LineItem can have one and only one Subaccount, but each Subaccount can have many Transaction LineItems. Each Transaction LineItem can have one and only one Account, but each Account can have many Transaction LineItems. An Account Type (i.e., Asset, Liability, Equity, Revenue, Expense) can have many Account Groups (i.e., Cash, Accounts Receivable, Capital Assets, Payables, etc.). An Account Group can have many Accounts. All object tables are grey table occurences in the Relationships Graph, Event Tables are in Green, Query Tables are in Red.
When I'm on the Transaction Layout to enter a new transaction, which has a portal to the Transactions LineItems table, I would like there to be a formula in the Transaction Type foreign key field that decides the following for any given transaction:
If any of the transaction line items in the portal uses a cash account that is being increased, then the transaction type is Cash Receipt.
If any of the transaction line items in the portal uses a cash account that is being decreased, then the transaction type is Cash Payment.
If any of the transaction line items in the portal DOES NOT use a cash account, but is affecting an expense account, then the transaction type is Purchase.
If any of the transaction line items in the portal DOES NOT use a cash account, but is affecting a revenue account, then the transaction type is Sale.
If any of the transaction line items in the portal does not meet any of the above critera, then the transaction type is General.
I'm not sure how to write the case or IF formula so that it considers this. In the meantime, I have always had to input the transaction type manually when I enter a transaction.
Everything else works as intended based on my current skill level (although you may find some inefficiencies which I am slowly learning how to resolve). As you can see from the screenshot, I have over 11,000 transaction details, over 4,000 transactions, and many analysis reports.
Any guidance to show the way would be very appreciated. I couldn't have gotten this far already without all the support I have gotten from this Forum and all my reading.