Counting enum list entries

Hi friends

l am developing a small app, where i need to count number of entries / selection a user does for Enum list in a column Named: “Application ID” while entering the values in a form.

i tried to use the following expression:
count([_thisrow].[Application ID]) , However i couldn’t get the count.

2X_6_65f57ab35c340f7b731c14d76fe9bb7939e63743.png

Issue -2 :
i have created a column "Allotted Time " type “duration” & i wanted to divide the value entered in this row with Number of counted entries in column “ID” which is pure number. However it is not going through.

Issue 3 :
i have a different forms BBF1,2,3 Etc. & a particular duration for each form in "Standard Manhours " column for that activity as below


in the form entry , when the specific form is selected , i wan the particular duration to be displayed without making any selection. how to get this done?

thanks in advance.

raju

1 21 3,513
21 REPLIES 21

LeventK
Participant V

@Raju_Tadepally
You can get the count with this:

COUNT(
	SPLIT(
		LOOKUP(
			[_THISROW].[KeyColumnName],
			"TableName",
			"KeyColumnName",
			"Application ID"
		),";"
	)
)

For issue#2, I’m not clear which number you want to divide with this count? [Batch] value?
Can you elaborate issue#3?

Thank you @LeventK
Issue 1 :
Thank you for the expression. i tried to use the following expression :
COUNT(
SPLIT(
LOOKUP(
[_THISROW].[Application ID],
“BBF1”,
“Application ID”, “Application ID”
),";"
)
)
but the result is not satisfactory. it is giving Zero as answer.

Purpose is to count number of items i select in Enumlist of “Application ID” column.

issue 2 :
i have two columns :
column 1 : Standard Manhours - Type : Duration in minutes
column 2 : Count from Enum list above - Type : Number
to do : divide column 1 by column 2
issue : the resultant type is not accepting as “Duration”

Issue 3 :
i have different tables :
Table 1 - Contains list of forms & standard Manhours against each forms
Table - 2 : contain the actual form in which this standard manhour is one column

required : need to bring the standard Manhour listed in Table 1 in to table 2 using reference and a valid if data validity function.
issue : it is displaying all values as appear in standard Manhours column irrespective of what is the form being used.
the Standard manhours is fixed for every form & when we open that form , the related standard manhours shall be displayed.

Hope i could clarify the point.
thanks in advance

May I ask:
1.) Is [Application ID] the key column of your table?
2.) Are you trying to perform this calculation whilst the user filling a form?
3.) What is the seperator for your Enumlist?

  1. yes - Application ID is the Key column
  2. Yes - once the user selects the data from Enum list , the app formula shall be computed
  3. separator is “;”

Thank you

@Raju_Tadepally
If [Application ID] is your key column and it’s an enumlist, may I ask how do you maintain the uniqueness of the Key Column?

Try with this:

COUNT(
	SPLIT(
		[Application ID],";"
	)
)

OR
Simply with this:

COUNT([Application ID])

I have changed the Key column to Row Number & used the suggested formula.

However i was struck with App formula.
the app shall compute number of Application ID’s selected in the Enumlist using the formula which i placed in Virtual Column called " Count no.of Trainees "& use it to divide the Duration derived from “Standard Manhours” column.


formula used in Virtual column is :
2X_1_1304db1cc81ccad8ae27227178c60069a89e0467.png
the result shall be in Duration.
App sheet is giving error message that
2X_4_485c650fa7914b0d45bc3b7d1a6bf4e6a931da47.png

Please guide.

@Raju_Tadepally
You are trying to divide a duration value with a number. Coverting that back to duration is a bit cumbersome but here it is:

RIGHT("0"&FLOOR(FLOOR(((HOUR([Standard ManHours])*3600 + MINUTE([Standard ManHours])*60 + SECOND([Standard ManHours]))/[Count no.of Trainees])/60)/60),2)&":"&RIGHT("0"&MOD(FLOOR(((HOUR([Standard ManHours])*3600 + MINUTE([Standard ManHours])*60 + SECOND([Standard ManHours]))/[Count no.of Trainees])/60),60),2)&":"&RIGHT("0"&MOD(((HOUR([Standard ManHours])*3600 + MINUTE([Standard ManHours])*60 + SECOND([Standard ManHours]))/[Count no.of Trainees]),60),2)

Oh, Such a big expression. quite cumbersome. However thank you @LeventK

Any insights on Counting enum list entries?
Thank you

@Raju_Tadepally
I have given that already 2 posts above

Thank you . But it is not counting the items after “;” delimiter.
further it has to count in real time when the enumlist is being selected.
hence i feel Thisrow is required.
please look in to.

@Raju_Tadepally
Can you post a screenshot from your Application ID column details? I have tested and it’s quite good working actually so there might be something missing. Can you also elaborate what do you mean with:

@Raju_Tadepally

I think the original problem with the COUNT() function was the usage of [_THISROW], it cannot be used in this case.

Since Application ID is an EnumList (already a list) the expression should simply be:

COUNT([Application ID])

… provided Application ID is defined as an EnumList AND you are trying to reference the count from an expression in the same table.

Count with [_THISROW] is ZERO

2X_2_295b60e1fcc272b1c1a4c90b643a611945ef054f.png

Count on same column (diff data) withOUT [_THISROW] is 3

2X_7_7f9b078b2903cee799205c8bc53dcbfda99f3723.png

2X_9_9f4da7531e5c4deb73d006b6ac2f2d273e527e83.png


Thank you.
2X_0_038bd0a32cca2632a95c72e904483aab8e10e41b.png
2X_a_a369e954c4db99f6d4d92494ca58bd9a0ae0c9a9.png
please have a look at the column screen shot.
when i select the different Application ID’s from Enumlist , the column “Count no.of Trainees” shall count the selected lists in real time before saving.
this Counted number in turn would be used to divide the Standard Duration of work to get per group duration.
i have tried with both methods suggested by you & Leventk.
but the result is showing still “1” for any number of selections in the enumlist , values separated by “;”
i have used this row because it has to count only the selection from the current entry & not from entire column in table.
Hope i am clear in explaining.
Thank you for your support. looking forward for resolution.
Raju

@Raju_Tadepally
Can you share your app with levent@able3ventures.com as a co-author so that I can check? Thanks.

Yes, let @LeventK take a look at your app. He’ll get you fixed-up in no time!

Thank you for your support. I have shared the app as co author.

issues at hand :

  1. counting the number of Application ID’s selected by user in the present form / present row ( Application ID is Enumlist which was referenced from other table called “form”)
  2. Divide the Time duration with the count obtained from this ( count was done in a virtual column) - i did some expression. it is giving some result in the form of decimal. as i couldnt get the result from the count , i have introduced another column - [No.of Trainees] to enter the detail Manually & use this for dividing the duration.( but iw ant this to be from count function.
  3. i use this [Allotted time] value to compare with total time taken for the activity & decide whether the candidate is with in time or exceeded Column 18. Here i face a issue of comparing duration with Decimal. any insight in this area?
  4. i face another issue - based on the form i use , the standard Manhours shall be populated automatically, but presently it is giving all available items in the list for manual selection.
  5. i tried to generate Names of the Trainees based on their Application ID’s in another column called [_Computedname] , i was not able to get names in Enumlist reference from table =form

Regards
raju

DONE - Your expression was malformed. Please pay attention to the expressions’ correct syntax and always perform a test before saving.

DONE - I had already given the expression for this calculation

DONE - You are trying to compare TIME value against a DECIMAL value. [Alloted Time] column shall be DURATION type, not DECIMAL.

DONE - You have only set the dropdown values for the [Standard Manhours] table, you haven’t set any Initial Value as per Form#

DONE

CONCATENATE(
	[Form No.],
	"-",
	SELECT(
		Form[_ComputedName],
		CONTAINS(
			SPLIT(
				[_THISROW].[Application ID],
				";"
			),
			[Application ID]
		)
	),
	"-",
	[Assessment No.]
)

Please note that all of the aforementioned changes have been made with BBF1 Table only. You can copy the same logic and related expressions to your remaining tables if needed.

dear @LeventK ,

Thank you for your Timely help. without your support , it is not possible for me to write such a big expression statement in Appsheet.

Thank you once again. i will take care of the rest of the app. Request your help incase of any further issues.

regards
Raju

You’re welcome

Count + Split works great, thank you very much!

Top Labels in this Space