Cashbook Structure Displaying Portals

[MariaAux]

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

[comment]

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?

[MariaAux]

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.

[LaRetta]

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.