First off, sorry if this question is too complex and/or involved for this forum, but I am having trouble getting my app to function correctly from where I currently have made it. I figure the easiest way to explain is just summarize what I am looking to create here, and maybe someone will have some insight to help get me unstuck…
“Ideally” I would like the form to be laid out something like this:
I am trying to create a simple commission calculator app, with really only two pages required, where a user inputs his or her sale and some corresponding info into a form and then view a statement of commission payments related to that user on dashboard page or something. The Record New Sale screen though is where it gets a little complicated, and even so I dont think it is “super complicated” to someone who has more experience with this than I do…“Ideally” I would like the form to be laid out something like this (which essentially would be in about three parts, sort of):
Screenshot 2022-06-23 185155
The SAVE button at the top would save all items within the form; the ADD LINE ITEM button would dynamically recreate/duplicate everything within that Line Item box and add it directly below it (in theory an infinite number of times, if one were so inclined); then within the Line Item box (or sub-form, if you will) there is a Split Commission section (effectivly a sub-sub form, of sorts) where someone could divide the commission between one or many other employees for that specific Line Item.
My table structure (at least as I currently have it, but I am open to input/advice here if something different is recommended) includes 5 tables: Transaction, Line Items, Comm Splits, Items, and Users. “Transactions” table has a column for ticket number and for date for the fields as in the above form then a relationship to “Line Items” table allowins many Line Items to one Transaction row. “Line Items” table has columns also for the fields you see above - Item, Value, Date, Description plus a relationship to “Comm Split” table which also allows meny Comm Split rows to one Line Item row, and also a percentage column column which brings in the corresponding percentage from the Item table (which exists as an updatable list of values), based on the dropdown selction of Item within the Line Items secton. Then the “Comm Split” table has two columns, Employee and Percentage, where the percentage field is a number field and the employee is generated from a dropdown selection linked to the users table as a list of values.
I have used the CRM app template to try and somewhat recreate the functionality for this, at least to dynamically add on the field groups, but can not seem to get it to function correctly.
I feel like what I am trying to create is relatively straight forward - with the exception of dynamically adding a sub form (or sub-sub form) and with the table referencing where A references B and B references C, then generates a table that filters table A referenced rows in table C (through table B).
Any input they can provide here that might push me over the hurdle I am at now, it would be greatly appreciated!!