Solved: Formula for expressing data in a table

Gardener76 · 07-10-2020 03:54 AM

Hi, I am looking for the best way to write the formula for the attached tables.

Table A, I will already have Neck Score, Leg Score and Trunk Posture Score.

Table C, I will have Score A and Score B.

Would I be correct in writing a formula like this for Table A?

IFS(
AND(CONTAINS([Neck Score], “1”),IN([Leg Score], “1”, IN([Trunk Posture Score], “1”)),1,
AND(CONTAINS([Neck Score], “1”),IN([Leg Score], “1”, IN([Trunk Posture Score], “2”)),2,
AND(CONTAINS([Neck Score], “1”),IN([Leg Score], “1”, IN([Trunk Posture Score], “3”)),2,
AND(CONTAINS([Neck Score], “1”),IN([Leg Score], “1”, IN([Trunk Posture Score], “4”)),3,
AND(CONTAINS([Neck Score], “1”),IN([Leg Score], “1”, IN([Trunk Posture Score], “5”)),4,

etc etc…

Just making sure I am not creating more work than is required and wondered if there was an easier way?

Thanks in advance, your ideas and input is always appreciated.

Gardener76

@Aleksi, I must say… you are an absolute legend!!! Thank you as always. It seems to be picking up the data as requested.

View solution in original post

Steve

Nothing in this makes sense to me.

Gardener76

Thanks for the input Steve?! Lol. Neither me… Hence why the question what would be the best way to formulate my request?

Gardener76

@Steve

Does this help?

I have the data for Neck Score, Leg Score and Trunk Posture Score, I need to create a formula which will allow me to get the Table A Score…I thought the formula above would do it, but it doesnt. So I wanted to find out the best way to do it.

Thanks in advance.

Steve

Part of the problem is that you cannot structure tables in AppSheet as in your images. Please provide screenshots from the app editor of the tables you have configured for your app.

Gardener76

@Steve sorry, I think I have confused the scenario. I know that I cant construct a table. I am trying to construct a formula to give me the table score based on the user input.

[Neck Score] (Type: Number)
[Leg Score] (Type: Number)
[Trunk Score Posture Score] (Type: Number)
[Table A Score] (Type Number: I am trying to construct a formula based on the values shown in the table. I dont want a table I just want the number)

Example:
User inputs:
[Neck Score] (1)
[Leg Score] (1)
[Trunk Score Posture Score] (1)
[Table A Score] (Would generate 1, If [Neck Score] = 1, and [Leg Score] = 1, and [Trunk Posture Score] = 1 then it can only be 1…

Does this make sense now?

Steve

Nope. Perhaps someone else can help.

Gardener76

Not a problem, thanks @Steve

I know a man who will nail this… @Aleksi would you be able to shed any light on this?

Aleksi

You need to create the spreadsheet as…

Then you can read the Score like
ANY(SELECT(TableA[Score],
AND(
[NECK]=[_THISROW].[NECK],
[LEGS]=[_THISROW].[LEG],
[TPS]=[_THISROW].[TPS])
))

Gardener76

Thanks @Aleksi

The formula would obvioulsly go in the App Formula of Table A Score, but the table would be in a different worksheet? How do I get the App Formula to recognize the table?

Gardener76

@Aleksi I have set up Tables in my spreadsheet as per above.

I added the spreadsheet with the table to the app as read-only.

I amended the column names in the formula to correlate with the Table, then inserted the formula in the Score Column. The Expression Assistant didn’t show any errors.

But it doesnt seem to be pulling the data and creating the Score.

Aleksi

You should have that formula in table B. Then you should have columns Neck, Legs and TPS in this Table B so you could read the value from the Score column in Table A.

Gardener76

Not sure I follow that .

Aleksi

If you are collecting data with the TableB and you want to save the score in that TableB, you need to calculate it in TableB. I’m assuming that your TableA is a read only table just for reference so you can calculate the score value when Neck, Legs and TPS values are filled in TableB.

Gardener76

Thats correct, thats what I have done.

But it doesn’t seem to work. The field that represents [Table A Score] which has the formula isn’t showing in the app.

Aleksi

What is the app name and account ID? Quicker to check the app directly…

Gardener76

Thanks Aleksi

App Name: MSK 360 Assessment
Account ID: 547968

Aleksi

Try…

ANY(SELECT(Table A[Posture Score A],
AND(
[Neck Score]=[_THISROW].[Neck Score],
[Leg Score]=[_THISROW].[Leg Score],
[Trunk Posture Score]=[_THISROW].[Trunk Score])
))

Gardener76

@Aleksi, I must say… you are an absolute legend!!! Thank you as always. It seems to be picking up the data as requested.

Aleksi

You could also use a dependent dropdown with you score fields like Table A[Neck Score] then Table A[Leg Score] etc. Then it will give just those numbers to choose that you have in Table A.