Help with IF expression

[Zee_W]

This solution might be on the community but am not seeing it.

I have an Enum List column where users can select multiple products called [Machine type]. I have created another column called [Category].

Depending on what items are selected in the [Machine type], I want a specific value to be entered into the [Category].

The expression I thought would work for [Category]:

IFS(
IN(LIST("Contoura", "TDR"), [Machine type], "Groundcare"),
IN(LIST("Cyclone", "Disc Mower"), [Machine type], "Agricultural")
)

But I'm being told the IN function is used incorrectly.

What am I doing wrong here? Any advice would be most appreciated!

[Nicholas_Malart]

Hey Zee_W,

You will not be required to use any IF() expression you can achieve this by using a lookup() or select() functions. 

For example :

SELECT(requiredtable[Category],[_THISROW].[Machine type] = [Machine type])

Or

Lookup([machine type], "requiredtable" , "machine type", "catagory") 

Now if there is multiple selections for the same machine type then you can use these in the suggested values you might have to wrap them in an Any() expression this will allow you to have an enum with multplie selections. 

Alternatively you could also place this into the valid if column if they both run consecutively after each other in the database. 

Place this in the Vaild if for the machine type: Requiredtable[machine type] 

Place this in the Vaild if for the catagory: Requiredtable[catagory] 

[Zee_W]

@Nicholas_Malart Thanks for the swift response!

Do you recommend creating a separate table for the LOOKUP() function? At the moment, the choices for the [Machine type] are part an EnumList and not from a separate table. Same with the [Category].

"Now if there is multiple selections for the same machine type then you can use these in the suggested values you might have to wrap them in an Any() expression this will allow you to have an enum with multplie selections. "

The values in the [Machine type] column will have more than one selection usually so how to insert ANY() in the formula?

[Nicholas_Malart]

As for wrapping an expression you can just do this. 

Any(SELECT(requiredtable[Category],[_THISROW].[Machine type] = [Machine type]))

If you need to reference data I would 100% create its own table just make sure you make it read only as the data can be stored on the appsheet server to recall faster as this data will not be edited.

Also you can still have it as a enum and keep those options in there and still reference a table with the same names though everytime you want to add a new option it means you need to add it within app sheets enum and then also the table. So it's best just to have it in one spot and call it from the table as needed saves on time and double handling. If you want to add or remove data without going to the table just make a little app on the side with this table where you can modify stuff with a pretty interface. 

[Zee_W]

Thanks, but the formula isn't producing the required result.

The data in the Machine Type won't be a single value, it'll be a list of values separated by comma (see table). Depending on what's in the first column, only one value will be in the second column (Category).

Machine Type	Category
Rollermower, TDR	Groundcare
Topper	Agricultural
Cyclone	Agricultural
Cyclone, Agri Range	Agricultural

Playing around a bit more the closes I got was this

IFS(
IN( [Machine Type] , LIST( Contoura, TDR, Swift, Contoura, Groundcare Range) ), "Groundcare",
IN( [Machine Type] , LIST( Cyclone, Cyclone Mower) ), "Agricultural",
IN( [Machine Type] , LIST( Slurry, Agitator) ), "Slurry Handling",
)

However, I think the app only recognizes if the values in the cell explicitly match my last. I have no way of knowing what will be inputted by my users.

Perhaps I'm looking for a formula that closely resembles look up feature in Excel where search a column of data with any combination of values similar to *text*.

Does this make sense?

=SUMIFS(C2:C62,$A$2:$A$62,"*facebook*")

[graham_howe]

In() is always going to look for an exact match because it is checking to see if the value is in the list, not whether there is something similar. You can consider using something like Contains() to check string fragments, but honestly using this sort of logic to compare against free form user entry is never going to be perfect.

IN()

Is item in list?

CONTAINS()

Does text contain fragment?

The better approach is to control the data entry by the user, providing dropdowns or push buttons to select a value rather than free text entry.

[Zee_W]

Thanks for the clarification @graham_howe . The reason we set that column for multiple values is because an administrator is fielding calls and selecting machines that potential customers have queries about. In fairness most often one machine is discussed but it the odd time there could be a couple mentioned. So the administrator captures this info for our sales rep to make the follow up call.

Probably wasn't the best setup to begin with but it was a vast improvement over the previous system!

I had a try at Container() and I kept getting an alert telling me invalid use of the expression 😓

[Nicholas_Malart]

I have to agree with what Graham has said having the users select and type their own input will just never work correctly to how you want.

I think I understand what you are asking the only way I think you will achieve this from my experience is. Can you make it the other way around by having the user select what type of catagory it is first and then have a enum list show what types of machine types you have for that catagory. So you can use a select or lookup in your suggest values. 

For example:

 

[graham_howe · Accepted solution]

Actually I was a little unfair, looking at the first post again, the [Machine Type] column is populated through an EnumList so as long as the list does not allow new entries, we can actually trust it. Therefore we need to compare the selected machine types with the 'lookup values'. I think the following should do it:

IFS(
  ISNOTBLANK(
    INTERSECT( [Machine Type] , LIST(Contoura, TDR, Swift, Groundcare Range) )
  ), "Groundcare",
  ISNOTBLANK(
    INTERSECT( [Machine Type] , LIST(Cyclone, Cyclone Mower) )
  ), "Agricultural",
  ISNOTBLANK(
    INTERSECT( [Machine Type] , LIST(Slurry, Agitator) )
  ), "Slurry Handling"
)

[Zee_W]

Yeah, in hindsight I should've set it up differently...without making major structural changes, I want to add a way to group the machines into catrgories so I can then create a filtered view in a dashboard, hence adding the [Category] column.

To answer the question about Cyclone vs Agri Range, technically Cyclone is a product within the Agri Range but sometimes a customer doesn't know what he's looking for so administrator inputs both. For purposes of the categorization I'm treating both as separate machine types that fall under Agricultural.  Does that make sense?

[graham_howe]

Check my response above, I think that might give you what you want with changing the data. Of course there are better ways, but not without creating another table and changing the way the drop down works a bit.

[Zee_W]

Ah, must have missed your previous post while responding! 

Intersect() is a new one for me! But the expression you have seems to have done the trick! I notice that I can't just use a fragment of text though, i.e. I'll have to write out each option in the EnumList, which is fine. But very helpful in any case.

Noted about making sure users can't add to the EnumList. I think it was allowed before but I'll disallow it now and tidy up the entries so they're consistent.

@graham_howe Thank you so much!

[graham_howe]

Yes Intersect() is pretty powerful but as with most list operations, it is looking for exact matches not fragments.

INTERSECT()

Items common to two lists

Part of the reason I never hard code items in an Enum or EnumList column is because I will often want to refer to them elsewhere. If you had a table of Machine Types with an ID, a name and a category, then you could reference that in the EnumList and the relationship to category would be automatic!

[Zee_W]

Definitely would change the structure if I had a do it over again. Huge learning curve then and still learning now! Thanks again!

[Nicholas_Malart]

Ahh no worries, I do believe Graham has come up with a solution that will solve your problem!

I am just going off my experiences and what I have learnt through my own app wih a CMS system so it's all a learning curve I am a year into it and learn something new everyday!

The community is great here and very helpful so I think it's good to try and give back. 

[Zee_W]

+1 on the great community...With all the features coming out definitely learn something new everyday! Thanks again for chiming in with your suggestions!

[Zee_W]

I hear you about not giving users too much free reign with data entry...small sacrifice to pay in order to get them on board to use the new system 👍