Many data entries when creating new row from form

[fedeyobe]

Hello! I hope you are having a nice day.

I'm having this problem: I use my app to manage a few (3) temporal rental apartments. I'ts been working really good but i want to improve the cost management and visualization.

I've added a "few" tables to register the costs of each reservation: 

1. Labor Costs
2. Utilities Costs
3. Travel Agency Commission Costs
4. Taxes Costs
5. Maintenance Costs
6. Administrative Costs
7. Amenities and goods Costs
8. Payment Method Costs
9. Extra Costs
10. Linen Costs

Also setted up an action (that executes like other 12 actions) when save a new reservation to set the id of each cost table from before to uniqueid() and also the id that relates the cost table entry to the reservation id.

Some of this costs tables also have sub-tables that have the information needed to calculate each cost, such as percentage of taxes, percentage of travel comission, etc.

Right now when i create a new reservation this two values are added to each cost table:

1. An unique id for each table value: uniqueid()

2. The id from the reservation linked to the new row of the cost table: [id_reserva]

So basically when I add a new reservation i have like 14 things being added to the datasource. I don't think this is the most effective way to accomplish this but i dont know how else to do it.

The idea after this is that I create another action for each table that executes and calculates the value of the cost when i add a new payment to any reservation.  Something like this:

For reservation A:

1. New payment added: Execute a form saved action that has the actions to calculate the costs for each table based on the sub-tables information.

What if i want to escalate my app to manage 20 or more apartments? i believe my app will explode lol.

Do you have any recommendations on how to improve or achieve this in a more effective way? i'been trying to accomplish this for a long time but i can´t come up with solution :(.

Hope you can help me.

Regards,

Federico.

[Marc_Dillon]

What do each of those 10 tables entail? Is it not something that you can simply add into the main reservation table as 10 (or more) columns?

[fedeyobe]

So basically each one of those tables respresent costs and each one have their own characteristics and properties, such as for example:

1. For the labour costs: i can set if the payment made to the cleaning person is based on a percentage of the reservation value or a fixed value. Or if the type of payment is based on comission or in a month basis payment. Also i set the payment method used.

2. For the taxes costs: there are many many taxes here and each one has their own characteristics, such as percentage, tax base, period of payment, etc. So i thought it would be better to have them on a separate tabe.

3. The payment method costs: each payment method has an unique % of comission, and not all the reservations are payed upfront but in two or more payments. So i have to log them on another table but they are linked to the reservation ID and the payments IDs for that reservation too.

These are just examples. But some of those costs have many special characteristics to just add them to the main reservation table. Also i think having them categorized on separated tables make the overall app look better and well organized (please correct me if im wrong in that).

I forgot to mention: i'm thinking about creating another table named "reservation_costs" that summarize each one of the costs from the costs tables so then i can just sum up all the columns and get the total costs of the reservation.

I believe i can make it work, but i feel like this way is not te best way to do this and i´m just brute forcing everything.

Any ideas are welcomed.

Federico.

[Marc_Dillon · Accepted solution]

Ok yah, some of those definitely make sense to have as separate tables. I'd probably lean towards trying to combine all of your cost tables into one table. Seems like it'd be simpler and easier to manage that way. Make sure to include a CostType column to distinguish them.

As far as making sure your app doesn't get overloaded with creating records, I'd maybe suggest looking into using the API to generate all of the cost records.

[fedeyobe]

Hello Marc, Thanks for your recommendations. I will look further into the appsheet API, i didn't even know there was one. 

Regards,

Federico.