my app can't find a "Text" type value when there are number AND text

[baba_sawane]

Hi everyone,

In my app I have an option that let the user enter a value, and that value is associated with forms. When they enter that value, they are able to see a list of all the answer of the form, dashboard, etc.

But I'm facing a problem : each form have a specific code which refer to a construction site. The format of those code is "00 X 000" (2 numbers / one letter / three numbers).

When I enter a code with only numbers, the filtering works fine.

But when I enter a code with only text OR text and number, the filtering does not work.

If anyone can help me out with that, I'll be thankfull !

[franc · Accepted solution]

when you need to connect two different columns in different tables, you need to create deferences.

https://support.google.com/appsheet/answer/10107396?hl=en

https://support.google.com/appsheet/answer/11618586?hl=en

also, when you use a column from a different table you need to use the name of that table, not only its column name otherwise Appsheet can't find it.

in order to check if your formula makes sense for Appsheet you can take advantage of the expression assistant

https://support.google.com/appsheet/answer/10104484?hl=en

this might also be helpful: https://www.youtube.com/watch?v=ujTCg-x80I0

[franc]

Could you please share the formula for filtering that you are using?

[baba_sawane]

Of course @franc . There is a screenshot of my slice.

For further information, on my sheet I use a formula in each row of each form worksheet. On these row, the value is equal to the code chosen by the user. With that formula, the filter will only show the form answers where the code value is equal to the value of the user, as you can see on that screenshot : 

Im a beginner in Appsheet. But if you have any solution that i can work on for my client, i'm willing to listen !

[franc]

Well, looking at your Excel, honestly, it's not clear to me that the problem with your formula affects only those cases where there is only one text. For example, the values 77 and 88 I don't understand how they can be related to 15A250.

Anyway, the formula you use checks an identity between 2 values contained in two different columns. An identity implies that what they contain is exactly the same value, and I don't see that 77 can be equal to 15A250 unless you first transform it with another formula, possibly in another column, either in Excel or virtual in Appsheet. From your previous explanation, I suppose that the CODE ACTUEL is computed in the spreadsheet through a formula you created. Correct?
Please let me know if I'm wrong but it seems to me that what you want is that when a user types a value, such as "25", Appsheet automatically uses this value to filter through the column CODE ACTUEL and selects the rows that contain this value. If so, to my understanding, you need to use another formula. If my assumption is correct and you want to check that the value, let's say "25", is in the CODE ACTUEL, I would write something like this to filter 

IN() or CONTAINS(), or I would use a Select() straight away.

see also https://support.google.com/appsheet/answer/10107681

Please let me know if my assumptions are correct and if this helps you to solve your problem.

[baba_sawane]

Thank you for your time and your answer franc. 

For your first assumption, yes, I have another worksheet with a column "CODE ACTUEL" with only one value, that the user selected.

That value is reported on each form worksheets with a formula inside google sheets. Then I use my slice to show only the forms where "CODE CHANTIER" is equal to "CODE ACTUEL"

But the second assumption isn't correct, i'm looking for the exact value. 

A code refer to a construction site and because my company have multiples entities, the format is not the same. This is why a code can be "77" or "15 A 183"

The main reason why i used this system with formulas inside my sheets is because, well, I did not find how to do it with a slice. I tried the Filter condition but I did not find how to connect "code chantier" in B worksheet, with "code actuel" in A worksheet.

I really appreciate your answer, and i'm still open to potential hint. I'll continue to work on my side. Good evening 

[franc · Accepted solution]

when you need to connect two different columns in different tables, you need to create deferences.

https://support.google.com/appsheet/answer/10107396?hl=en

https://support.google.com/appsheet/answer/11618586?hl=en

also, when you use a column from a different table you need to use the name of that table, not only its column name otherwise Appsheet can't find it.

in order to check if your formula makes sense for Appsheet you can take advantage of the expression assistant

https://support.google.com/appsheet/answer/10104484?hl=en

this might also be helpful: https://www.youtube.com/watch?v=ujTCg-x80I0

[Suvrutt_Gurjar]

Hi @baba_sawane 

This is not about your current issue because @franc is helping you. Just wanted to bring your attention to the column name you have given in the slice. 

The column name is very big and contains brackets and even a colon is there. While AppSheet is very lenient most of the times in accepting special characters in various elements such as slices, table and columns, you may want to consider avoiding such column names. If you are using such column names in expressions, it could confuse one while debugging the expressions and at times even give error as expressions also use brackets etc. 

Best practice will be to avoid special characters except some like underscore(_)  in element names and keeping those names short. 

[baba_sawane]

Thank you for your advice, I will update the name of my headers ASAP.

[baba_sawane · Accepted solution]

Hi everyone. 

After thinking about the system i'm trying to make, I think this is not the simpliest way to do it, so I'm trying another solution : 

Instead of using the slice for everything (dashboard, graphics and showing the list), i'll only use the slice for my graphics (it's not impacted by my problem so it's fine).  Now i will not be doing a filter, but i'll show everydata to the user.  Based on Search using a form , I will use an action to filter each of my forms. 

There will be the list of my forms on one menu, and when the user access it, he will be required to write the code he's looking for. I'm using a separate menu for my dashboard. 

For now, i'll try a demo and then i'll try to do @franc suggestion with Ref columns, I do think it will work. Main difference would be user interface, so I will do both to propose to my company.

Anyway, thank you a lot for helping me ! Have a nice day