Query about Database architecture (Parent Child Table)
Hello friends,
I am writing an app (Personal Expenses Manager). There are 4 tables in the app (Categories Table, Expenses Table, Spending Table, Payments Table)
Categories -- For grouping the expenses under categories
Expenses -- Individual expenses
Spendings -- For recording the payments on each expense. Sometimes there can be multiple payments for each spend on an expense (eg: Rent payment weekly for the same month)
Payments -- For recording every single payment. Sometimes multiple payments for a spend (as above)
Problem:
I am facing problem in designing a form on the Spendings table where I want to have a section for the Payments table so that when I enter the values in those payments table fields and press "Record Payment" button, an entry gets created in the payments table. Basically I am trying to have form fields from the payments table on the Spendings table ('cuz I don't want to duplicate the fields in the Spendings and Payments tables)
Current Table architecture for spendings table is attached below. I want to shift all the fields below "Payment Details" heading in the Payments table instead of having them in the Spendings table ('cuz those fields are directly related to taking a payment and it makes more sense to have those fields in the payments table rather than having them in the Spendings table and then duplicate the same fields in Payments table)
8 replies
-
I'm sure you know how to create new fields in Payment Details. You know how to create new records and copy data. I'm not sure what the question is.
-
said:
Firstly, I want to know how can I avoid first entering details in Spending Table (eg: "Amt Paid", "Pmt Mode", "Bank Name", "Paid By" etc...) and then copy all these fields into the Payments Table (when I click the "Record Payment" button). This will be duplicity of data.My response is to separate data from UI. Create dashboards/pages where you create the UI to enter in data. Then your Spending and Payments tables just store the data.
Either way you will have to have two sets of fields, one in Spending and one in Payments or one in the dashboard and one in Payments/Spending.
To continue your design. You can keep the Payments field to be used as data entry fields. Then have a button that will copy the data to Payments. The button will also null out the Payment fields in Spending so you don't accidentally copy unwanted data. You can hide the fields/button with a Yes/No field that is called "Enter Payment". So the fields are hidden when you don't need them and appear when you do.
Secondly, I want to be able to create records from within the Payments Table also by selecting the spending against which the payment is being made.
I'm not sure what you mean. When you are in Payments and create a new record there is no link to Spending so you have to manually create that link. I would recommend that you create just one place to do your data entry, like a dashboard.
-
Here is an example of showing/hiding fields:
The Create Rider Line is set to No so all of the associated fields are hidden. But when I click on Yes:
Now I can enter in data that will be stored in the Results table.
-
Thanks for the reply mate. Actually I have never worked with pages, so have no idea how they work. I understand that I can put the standard fields on a page and then have the data entered into the desired tables from those fields. Just don't understand how to deal with the relationship fields !!
eg: My Spendings table has a (M:1) relation with Expenses table (Each expense will have multiple spendings over the period of time --- rent, utility bills paid every month).
So in oder to use the relationship field named "Expense Name", I just create a relationship field on the page as normal and then in the code how do I assign that relationship field to the actual relationship filed in the Spendings table when copying data to the Spendings table.. Is it going to be something like:
let newSpending := (create Spending); newSpending.('Expense Name' (column in Spending table) := 'Expense Name' (field on the page); 'Due Date' (column in Spending table) := 'Due Date' (field on the page))And I assume that the 'Spend ID' field will get auto filled in the Spending table as soon as new Spening record is created as above.
Please correct me if I'm am wrong and guide me in the right direction.
Thanks,
Vermaji
---------------------------
-
-
Hi
You all but answered you own question in your annotation. If I were writing this I would have Spending and Payments in the same table as they both share the same properties. I would then have a table called Reconcile as a join table with two instances of your combined transaction table joined to it. This way you can combine spending and payments together to your hearts content to reconcile them.
Regards John
-
said:
Actually I have never worked with pages, so have no idea how they work.Just think of a Page as a table with just one record, which is what we do when we create a dashboard. Saves you a few steps and doesn't allow you to accidentally create more records.
My Spendings table has a (M:1) relation with Expenses table
Ok, that is good information to know. When you are scripting record creation of a child (M) record and want to link to the parent (1) record you need to have the recID of the parent available in a variable to make the link.
If you have a Page that creates a new Spendings record, you will probably have a dynamic choice field where you select the Expense you want to create the Spending under. Your code would look something like:
let expenseRecSelected := record(Expenses,number(expensedynamicfield)); let newSpendingRec := (create Spendings); newSpendingRec.( Expenses := expenseRecSelected )If you are scripting the creation of a new parent record and new child records, then you need to create the parent record first in a variable, then use that variable to link all your child records.
Content aside
- yesterdayLast active
- 8Replies
- 38Views
-
3
Following