Where

Marco_Bonucci · 11-18-2021 06:25 AM

Hi I have no idea how to use the count function to calculate how many times a word appears in my table. This would be the solution to another problem: I have a EnumList column which can be completed with more sentences, but the chart counts the rows with more things selected as an additional data. How could I do?

SkrOYC

Make a new table called something like “Interventions”. This table should have the following schema:

KEY	INTERVENTION
UNIQUEVALUE-1	INTERVENTION-1
UNIQUEVALUE-2	INTERVENTION-2
UNIQUEVALUE-3	INTERVENTION-3
UNIQUEVALUE-4	INTERVENTION-4
UNIQUEVALUE-N	INTERVENTION-N

Add the table to your app and make sure the Columns are properly configured (Both should be text, Key should have an UNIQUEID() Initial Value and Intervention should be blank, maybe marked as Required)
Now, here you have 2 options. To create the list of Interventions from your data or from AppSheet:
1. From your data: Add the same list of interventions that you have inside your EnumList’s column config on the Intervention column of the new table. Each Intervention is a different row.
  When there are no Interventions left, you can create Keys for them using the following article:
  https://help.appsheet.com/en/articles/1726606-manually-generating-uniqueid-key-values
  Don’t forget to paste the results of the formula so that the plain text will be saved and not the formula itself
2. From inside your app: Create a view (table, deck, card, whatever) based on your new table and inside that view you will be able to add your Interventions. Add all of the ones that you have inside your EnumList’s column config
Now that your new table is populated with the same options that were inside your EnumList’s column config, you can change where the options come from
Go inside your EnumList column config, delete all the options that you made and change it’s Base Type to Ref. Then, on Base type details select your new table on “Referenced table name”. Should look like this:
Now, you have two other options (use either one):
1. Scroll down to Data Validity and inside Valid If write YOURNEWTABLENAME[KEY]
2. Scroll down to Auto Compute and inside Suggested values write YOURNEWTABLENAME[KEY]
Now, at this point you have to save your changes. Maybe you already did it. Next:
Go to your new table columns and add a Virtual Column called something like [TIMES_USED] and add this as AppFormula:

COUNT(
	FILTER(
  	"THE_NAME_OF_THE_TABLE_WHERE_THE_USERS_ADD_ROWS_AND_WHERE_THE_ENUMLIST_IS",
    IN(
    	[_THISROW],
      [THE_ENUMLIST_COLUMN]
    )
  )
)

Change your chart’s For this data config to the new table. Chart type as histogram. Group aggregate SUM : : THE_VIRTUAL_COLUMN_CREATED_EARLIER. Chart columns, the Intervention one (This is assuming you are not testing the new chart editor)

Post here if any problems/questions

PS: You will need to change all of the previous records from the EnumList that before were the name of the intervention itself. Now they need to be the Key value from the new table. It should be very easy using “Search and Replace” kind of feature from Sheets or Excel

View solution in original post

SkrOYC

Marco_Bonucci

What should I do with Split? If I put it in the EnumList column I can’t choose sentences anymore

SkrOYC

You need another column to count how many times a word appears in your table.
You could have this on a VC of Number column type or VC of Show column type.
We need more info in order to help

Marco_Bonucci

Should I create a new VC?

Marco_Bonucci

What kind of informations do you need?

SkrOYC

Where, when, why you need to count how ma…?

Are you trying to make a chart?

Marco_Bonucci

I did and it count the rows in a EnumList column. The problem is that when I select, for example, two occurrences in the EnumList, the chart doesn’t count 1 of the first and 1 of the second occurrence but create a third data which is “First occurrence, Second occurrence”

SkrOYC

You should divide this problem into two parts.
The first problem gets solved using the expressions mentioned above
The second one is about chart.
Charts are not easy to do here, at least there have been always some hiccups.
Please post here your chart, how it looks today, how you want it to look, the table schema that the chart is based of, etc

Marco_Bonucci

Sure. The chart will be in Italian cause I am.

Marco_Bonucci

As u can see the column of the chart I selected refers to “Profilo Silver - RT -, Intervento su RT, XML7”, but I want it to count as one “Profilo Silver - RT -”, one “Intervento su RT” and one “XML7”. In fact you can see that those occurrences already are under the chart

SkrOYC

“COUNT” from the Chart View has nothing to do to COUNT() (the function/expression)
Can you elaborate better what the columns represent, where those values are saved, the schema (pending), and others?

Marco_Bonucci

Each column of the chart should represent the time a kind of Technical Intervention is done. Every time a technician do a Technical Intervention fill a form. All the values are saves in a Google Sheet in a column called “Intervention”. If the Interventions put in the form are more then one there is this problem

SkrOYC

Let me see if I guest this correctly:

You want to show in a chart how many times a certain Intervention was made.
There is an Inspection table of some kind where a user adds a row and at some point there is a column called “Intervention” that’s an EnumList where he/she can select the kind of technical intervention or interventions that he/she is making.

Where do the Technical Intervention list of options come from?
If there is a fixed list, this list should be on it’s own table and there should be a Virtual Column counting how many times this Intervention is used/selected

Marco_Bonucci

The list is in the Intervention column: there are some preselected values and each Technician can choose the preselected values or write their own

SkrOYC

Where

Inside the EnumList config?

Marco_Bonucci

Exactly

SkrOYC

Make a new table called something like “Interventions”. This table should have the following schema:

KEY	INTERVENTION
UNIQUEVALUE-1	INTERVENTION-1
UNIQUEVALUE-2	INTERVENTION-2
UNIQUEVALUE-3	INTERVENTION-3
UNIQUEVALUE-4	INTERVENTION-4
UNIQUEVALUE-N	INTERVENTION-N

Add the table to your app and make sure the Columns are properly configured (Both should be text, Key should have an UNIQUEID() Initial Value and Intervention should be blank, maybe marked as Required)
Now, here you have 2 options. To create the list of Interventions from your data or from AppSheet:
1. From your data: Add the same list of interventions that you have inside your EnumList’s column config on the Intervention column of the new table. Each Intervention is a different row.
  When there are no Interventions left, you can create Keys for them using the following article:
  https://help.appsheet.com/en/articles/1726606-manually-generating-uniqueid-key-values
  Don’t forget to paste the results of the formula so that the plain text will be saved and not the formula itself
2. From inside your app: Create a view (table, deck, card, whatever) based on your new table and inside that view you will be able to add your Interventions. Add all of the ones that you have inside your EnumList’s column config
Now that your new table is populated with the same options that were inside your EnumList’s column config, you can change where the options come from
Go inside your EnumList column config, delete all the options that you made and change it’s Base Type to Ref. Then, on Base type details select your new table on “Referenced table name”. Should look like this:
Now, you have two other options (use either one):
1. Scroll down to Data Validity and inside Valid If write YOURNEWTABLENAME[KEY]
2. Scroll down to Auto Compute and inside Suggested values write YOURNEWTABLENAME[KEY]
Now, at this point you have to save your changes. Maybe you already did it. Next:
Go to your new table columns and add a Virtual Column called something like [TIMES_USED] and add this as AppFormula:

COUNT(
	FILTER(
  	"THE_NAME_OF_THE_TABLE_WHERE_THE_USERS_ADD_ROWS_AND_WHERE_THE_ENUMLIST_IS",
    IN(
    	[_THISROW],
      [THE_ENUMLIST_COLUMN]
    )
  )
)

Change your chart’s For this data config to the new table. Chart type as histogram. Group aggregate SUM : : THE_VIRTUAL_COLUMN_CREATED_EARLIER. Chart columns, the Intervention one (This is assuming you are not testing the new chart editor)

Post here if any problems/questions

PS: You will need to change all of the previous records from the EnumList that before were the name of the intervention itself. Now they need to be the Key value from the new table. It should be very easy using “Search and Replace” kind of feature from Sheets or Excel

SkrOYC

@Marco_Bonucci
Side note: if you find hard to do the keys thing, you can actually write any text string, as long as it’s unique in the context of that column. The formula just makes the same as UNIQUEID() does inside your app but it’s not mandatory

Marco_Bonucci1

Hello it’s me, I have done the tutorial

Marco_Bonucci

Hi! That’s perfect but I’d want to filter by date (Column Data on Prima Nota) on the chart. How could I do?

SkrOYC

Marco_Bonucci

Yes but on the chart it only make me filter by columns on the new table I created (with Interventions)

SkrOYC

You can only filters using columns from the table the charts is made from

Marco_Bonucci

Is there any method to filter by a column in the other table? I really need it

SkrOYC

I think this is your best bet

Marco_Bonucci

OMG That’s hard

Marco_Bonucci

What about counting them only if they are in the current mont? Is it simpler?

Count how many times a word appears in my table

Where