Check Mechanism for Percentage Total of Several Ro...

atanghoneycomb · 01-26-2020 11:57 AM

Dear Appsheet Masters,

I am creating a simple cash-out app for servers, bartenders and promoters at our rest./bar/club entertainment center.

Some big cash-out (total tip of a master bill) will be shared by several people. I want to maker sure the total of the percentages they put it under one cash-out code equals 100%. The entries of the same cash-out share the same cash-out code.

In excel, it is very easy to do. I have a “% Check” column and the formula is “=IF(SUMIF(C:C,C2,G:G)<>100%,”% Split Wrong (Split Toatl Should be 100%)","% Split Correct")". However, in appsheet I don’t not how to select a specific cell in a appsheet formula like I select “C2” in the Excel formula above.

The excel sheet format is shown below:

LeventK

@atanghoneycomb
You can provide the same with using SUM(…) and SELECT(…) expressions:

SUM(
	SELECT(
		Tablename[SumColumnName],
		[Cash-out Code]=[_THISROW].[Cash-out Code]
	)
)

atanghoneycomb

Thank you very much Levent! I see “_THISROW” solves the problem here, but if the cell that should be equal to is not in this row?

LeventK

@atanghoneycomb
When you set up this expression in a virtual column, it will sum the values in the selected table from the selected column for each row value. When you test the expression in the expression editor, you will notice that the expression will evaluate to the same sum value for each row where [Cash-out Code] value is the same or identical.

For example if you sum [Cash] column, the given expression will yield to below values for each row (assuming that the table has only 3 rows as a sample😞

[_RowNumber]	[Cash-out Code]	[Cash]	[Virtual Column Value]
1	ABC123	1,000.00	2,000.00
2	ABC123	1,000.00	2,000.00
3	BCD234	1.000,00	1.000,00

atanghoneycomb

Thanks Levent! I have another question. Is there a way to add an instruction for each data entry for the app? For example, by default, when an app user puts data into “Date” field, the app will have an instruction that says “dd/mm/yyyy” above the blank so the user knows the format of the entry. Can we add customized instructions that are not included in the column name?

LeventK

@atanghoneycomb
In the column structure Description property is for that reason. The Description property only appears in the Form View but in Detail View you only see the Column Name (or Display Name property if you have assigned). You may want to check this page as well.

Check Mechanism for Percentage Total of Several Rows