Happy New Year to Everyone!

I wonder if you could be so kind as to set me in the right direction. I am having issues getting the database structure right in a very simple Bank Cashbook displaying the Monthly Deposits and Withdrawals. My Cashbook Layout lists the Month and Year in it's two fields with a Portal for Deposits and Portal for Withdrawals which should display all the Deposits and Withdrawals for that month only.(Screenshot attached)

I only want to work in the Cashbook Layout so my problem arises that because the tables are related by a calculation, I can't add a new Portal Row to create a new record in the Deposits/Withdrawals Tables without a Script. The script seems to work sometimes, and other times not. I have attached a Screenshot of the Script.

Is there a way to relate these tables without a calculation (in order to add new records into the portal easily), but still display only the Deposits/Withdrawals for a single month at a time?

My Tables are as follows;

CASHBOOK TABLE

Month

Year

DEPOSITS TABLE

Deposit Description

Deposit Amount

Deposit Date

cDepositMonth (MonthName ( Deposit Date )) ***Related to Cashbook Month***

cDepositMYear (Year ( Deposit Date )) ***Related to Cashbook Year***

WITHDRAWALS TABLE

Withdrawal Description

Withdrawal Amount

Withdrawal Date

cWithdrawalMonth (MonthName ( Withdrawal Date )) ***Related to Cashbook Month***

cWithdrawalYear (Year ( Withdrawal Date )) ***Related to Cashbook Year***

Thanks so much in advance and all the best for the New Year!

Maria

Is there a way to relate these tables without a calculation (in order to add new records into the portal easily), but still display only the Deposits/Withdrawals for a single month at a time?

Actually, there are ways you could do this - but it raises the question of what date (if any) should be auto-entered in a new record created in the portal. Presumably you'd want the currently viewed year and month - but which day?

---

BTW, you are not seriously contemplating having separate tables for deposits and for wihdrawals, are you?

BTW, you are not seriously contemplating having separate tables for deposits and for withdrawals, are you?

Mmmm, ummm, yes, at this very moment, I have got separate tables. They are each related by Month + Year in separate Portals. I I were to combine them into one Table, How could I display the Deposits in one Portal Table and the Withdrawals in another Portal Table?

Thanks for the advice.

Hi Maria,

"How could I display the Deposits in one Portal Table and the Withdrawals in another Portal Table?"

You would include a Type field where you specify either Deposit or Withdrawal but, depending upon your fields, it could be a calculation as: Case ( Amount < 0 ; "Withdrawal" ; "Deposit" ). Your portal could also just filter based upon Boolean of Amount < 0 for withdrawals.