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.
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
thanks in advance.
raju
@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?
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.
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:
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
Count on same column (diff data) withOUT [_THISROW] is 3
Thank you.
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 :
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!
User | Count |
---|---|
32 | |
26 | |
25 | |
22 | |
17 |