I am trying to make a commission tracking app that in my head I think should be straight forward, but, it is not working out to be lol. In a roundabout way, I think the way I am structuring it shouldn’t bee too far off from creating a standard invoice with line items, but I think the line item sub lines is what is throwing me off.
Also, if I have the data schema set up in a way that is not ideal either please let me know, I am open to modifying the structure however is needed to make it work as efficiently as possible.
To start, I have 4 general tables:
comm_record
id
invoice_number
sale_date
paid_date
line_item
id
comm_record_id
service_name
value
percentage
gross_comm
split_comm
id
line_item_id
user
percentage
net_comm
service
id
name
description
class
comm_percentage
Then the rough form I have set up like this:
My thought was to have the first section (Invoice/dates) save into session storage as value changes. Then on the service row, the select input pulls the data from the service table, and the user inputs the total dollar amount for that sale item, and the row below would be a form repeat that defaults the current user and 100% in the text field, but if they click the add button on the right it repeats that row where someone could select a 2nd (or 3rd) user and change the percentages accordingly. Once all of that is filled, they can click the add button above, it saves two arrays, the first array would have all of the service line items in it, and the second would have each service line items corresponding user percentage split data and then displays it in the table or repeat below. This is the main step where I am getting hung up – is this the correct usage for an array, either for both or just one of the two-line items here, or is there a better way to go about doing this, like saving as a second object (or array there) in session storage, or something else entirely?
Lastly, after that data is stored however is best, user clicks save and it then:
- saves all of the info from the top section into the comm_record
- saves all of the individual line items (the upper row of the four fields) into the line item table, all
referencing the one comm_record saved in step 1., as well as taking the percentage from the
selected service, multiplying it by the value input and saving it in the gross_comm field. - then, just the same as in 2., it saves each item from the second row into split_comm, which each
referencing its respective line item as saved in step 2., and also like it 2, this would take the entered
percentage and multiply it by the gross percentage from its line_item to get the net_comm field. - Finally, which I don’t need help with this part, on a separate page the current user would be able to
see a table with his specific totals of the net_comm amounts associated with him, and filter by date
(which should be accessible through a double table join in the query to expose the respective date
from the comm_record table, right?)
The data structure ultimately needs to be saved like:
{“comm_record”: {
“id”: 1,
“number”: 139555,
“sale_date”: xxxxx,
“paid_date”: xxxxx,
“line_item”: {
“id”: 1,
“comm_record_id”: 1,
“service_name”: “service 1”,
“value”: 123.45,
“percentage”: .12,
“gross_comm”: valuepercentage=14.81,
“split_comm”: {
“id”: 1,
“line_item_id”: 1,
“user”: user1,
“percentage”: .75,
“net_comm”: line_item.gross_commpercentage=11.11
},
“split_comm”: {
“id”: 2,
“line_item_id”: 1,
“user”: user2,
“percentage”: .25,
“net_comm”: line_item.gross_commpercentage=3.70
},
},
“line_item”: {
“id”: 2,
“comm_record_id”: 1,
“service_name”: “service 1”,
“value”: 555,
“percentage”: .2,
“gross_comm”: valuepercentage=111,
“split_comm”: {
“id”: 3,
“line_item_id”: 2,
“user”: user1,
“percentage”: .25,
“net_comm”: line_item.gross_commpercentage=27.75
},
“split_comm”: {
“id”: 4,
“line_item_id”: 2,
“user”: user2,
“percentage”: .50,
“net_comm”: line_item.gross_commpercentage=55.5.
},
“split_comm”: {
“id”: 5,
“line_item_id”: 2,
“user”: user4,
“percentage”: .25,
“net_comm”: line_item.gross_comm*percentage=27.75
}
}
}
Where the net pay for users associated with invoice #139555 is:
User1: 38.86
User2: 59.20
User4: 27.75
Hopefully all of this makes sense (or isnt too much information lol), but any help in getting my going in this direction, specifically in Wappler, will be greatly appreciated!
Last updated: