Simple Cashbook

[emtau]

I'm trying to create a simple Cashbook Database which goes like this:

In_Table: (Form View Layout showing records from In_Table)

Fields:
Date - records the date of Incoming Transaction
Details - records the details of a Transaction
Amount - records the Amount in $$ of a Transaction
SumIn - sums up all current foundset of In_Table records

Out_Table: (Form View Layout showing records from Out_Table)

Fields:
Date - records the date of Outgoing Transaction
Details - records the details of a Transaction
Amount - records the Amount in $$ of a Transaction
SumOut - sums up all current foundset of Out_Table records

Cashbook_Table:

Now my question is in this Cashbook Table. I'm not sure what the relationships will be between other Tables and this one and what Fields are needed to be created in this Table, but what I'm trying to accomplish is this:

The user will only have the options to Enter Transactions whether In Incoming or Outgoing Transactions independently from the respective In_Table Layout (Form View) and Out_Table Layout (Form View). The Cashbook will be able to record both Transactions and record them in Rows with a Balance Field at the bottom. For example, as soon as the User enters an Incoming Transaction from the In_Table Layout, it will automatically be populated in the Cashbook Table and same goes with the Outgoing Transaction. So Users can View the Cashbook Table but cannot do entry from there.

How can I go about doing this?

Uploaded is my sample file for clarification.

 

Cashbook.fmp12

[comment]

IMHO you only need one table for all of this. If you want, it can have separate fields for entering Amount In and Amount Out (in such case, you will also need a calculation field that combines them by changing Amount Out to negative - and this will be the field you will want to summarize by a summary field in order to display a balance). You can also build separate layouts for incoming and outgoing transactions, both based on the same single table.

I could not understand the requirement to prevent users from entering data directly into this table. I believe that any restrictions you want to place upon them can be implemented using a combination of user privileges and layout measures.

 

[emtau]

6 hours ago, comment said:

IMHO you only need one table for all of this. If you want, it can have separate fields for entering Amount In and Amount Out (in such case, you will also need a calculation field that combines them by changing Amount Out to negative - and this will be the field you will want to summarize by a summary field in order to display a balance). You can also build separate layouts for incoming and outgoing transactions, both based on the same single table.

Thanks for your response comment, however i did tried that first approach and I wasn't sure if it was the right way since I noticed that when I created the Layout for "IN"s and the "OUT"s separately with their respective Fields from the same single table. The records shown on the "IN"s Layout also shows records from the "OUT"s Layout so I figured it was the right way to separate them so the "IN"s and "OUT"s records are independent. But you're right this might have been easier for the Cashbook Layout since no relations are needed. So I guess I'll try that again and build on it and see if it works with what I expected.

I guess I will try and combine them all on the same Layout and see how it works out.

Any hint on the best approach to do this will be much appreciated.

[comment]

5 minutes ago, emtau said:

The records shown on the "IN"s Layout also shows records from the "OUT"s Layout

Layouts do not filter records. If you want to see records of a specific type only, you must perform a find (you can do this automatically by using a OnLayoutEnter script trigger). Or use a portal.