How to populate a table column based on a form input

[shachiak]

Hi. I'm new to appsheet. I have three tables, the first is Member Details which contains the member information. The second is Produce Deposited which has a record of each time a member has deposited produce with the coop along with the weight of the produce. The third is Input Collection which keeps a record of all the inputs the member has collected on credit. I want to calculate the payout the coop makes to the member based on a rate inputed by the coop. I want the coop to input a rate (e.g 47) and the payout table automatically calculates the total recievables to the farmer (rate*total weight of produce), calculates total deductibles of the member (total monetary value of inputs collected), and subtracts deductibles from receivables to get the net payout to the member. I want this done automatically for all the members once the coop has inputted the rate/kg on the app

[rmsmeltz · Accepted solution]

@shachiak 

A potential solution:

Here's how you can set up the payout table to automatically calculate the total receivables, total deductibles, and net payout to the member based on the rate input by the coop:

1. Create a new table called "Payout" that will hold the calculated payout information for each member.

2. In the "Payout" table, add a column called "Member ID" that links to the "Member Details" table. This will allow you to pull in the member information and calculate the payout for each member individually.

3. Add a column called "Rate" to the "Payout" table that allows the coop to input the rate/kg.

4. Add a virtual column called "Total Weight" to the "Payout" table that calculates the total weight of produce deposited by the member. This can be done by using the "SUM()" expression to sum up the "Weight" column in the "Produce Deposited" table for each member.

5. Add a virtual column called "Total Receivables" to the "Payout" table that calculates the total amount owed to the member based on the rate and total weight of produce deposited. This can be done by using the expression "Rate * [Total Weight]".

6. Add a virtual column called "Total Deductibles" to the "Payout" table that calculates the total monetary value of inputs collected by the member. This can be done by using the "SUM()" expression to sum up the "Value" column in the "Input Collection" table for each member.

7. Add a virtual column called "Net Payout" to the "Payout" table that subtracts the total deductibles from the total receivables to get the net payout to the member. This can be done by using the expression "[Total Receivables] - [Total Deductibles]".

8. Set up a workflow rule that triggers when the coop inputs a new rate/kg in the app. This workflow rule should update the "Rate" column in the "Payout" table and recalculate the "Total Receivables", "Total Deductibles", and "Net Payout" columns for each member.

9. Test the app to make sure that the payout table is automatically updated and calculates the correct payout for each member based on the rate input by the coop.

By setting up the payout table with virtual columns that automatically calculate the total receivables, total deductibles, and net payout for each member based on the rate input by the coop, you can streamline the payout calculation process and reduce the chance of errors. The workflow rule ensures that the payout information is updated automatically whenever the rate is changed, which saves time and effort for the coop.

[shachiak]

Thanks! I've been able to do everything apart from automatically fill all the rows in the "Rate" column based on user input. I tried creating an action but it gives me only the option to update the current row in the "Rate" column and not the entire rate column

[shachiak]

P.S. The payout rate is the same for all members

[shachiak]

I was able to do it by selecting all members before inputing the value of the Rate. Thanks!