Referencing tables - help

[Tiger1]

I have an app that uses two tables.

Table A - Results of test are done here
Table B - Instructions are here based on which results the patient got

I need Table A - to show instructions from Table B - based on the results that the patient got in Table A:

Example:
Patient A gets a “Negative” result. I need Patient A receiving instructions from Table B (for “Negative” results.

[WillowMobileSys]

I assume you are going to want to show the list of instructions in a nice list. I’ll assume that

• you have all of the instructions pre-entered into Table B.
• instructions are labeled with Test Result of “Positive” or “Negative”
• instructions listed for several conditions.
• There is an Order column so instruction are in proper order.

I would create a Virtual Column defined as “List” and use an expression similar to:

ORDERBY(
        SELECT(Table B[Row ID], 
               AND([_THISROW].[Test Result] = [Test Result],
                   [_THISROW].[Condition] = [Condition]
               )
         ),
         [Order]
)

You can then define an Inline view to show these rows in a table form on the Patient Detail View

[Tiger1]

My company is going to NOW be testing for Corona virus - here. And they wanted me to build an app that could AUTO text/email patients the results…

anyway…ugh.

I tried referencing with a key (TABLE A):

But this did not work…

[WillowMobileSys]

If Table B is that simple, then you only need a single Long Text column in Table A and then use a LOOKUP function to pull in the text blob you want. Something like this:

LOOKUP([Results], "Table B", "Result", "Instructions")

[Tiger1]

Virtual?

[WillowMobileSys]

I don’t think I would only because it will slow Sync time and if you have lot of patients, the app will get slow very quickly.

[Tiger1]

ok. Thanks. Let me try this…

[Tiger1]

I must have something wrong. Still not pulling anything from Table B - Instructions Table.

Table A:

Table B:

[WillowMobileSys]

Show what you have done! Now, I assumed that [Results] as shown in your Parent Table definition is where the patient result is assigned.

[Tiger1]

Yes. Table A - [Results] is the column where the results are assigned. Table B - has Column [Result] - with the two options - “Negative” and “Positive”.

I referenced Table B - with column [Instruction key].
I also have column [Instructions] in Table A - this is where i put your expression:

LOOKUP([Results], “Table B”, “Result”, “Instructions”)

I have a view - table - that shows all patients with results. But the instructions are not there. It shows the column - but no data.:

[WillowMobileSys]

Set Instructions in Table A as “Long Text”

Also, make sure you are not confusing “Instructions Key” with “Instructions”!!

[Tiger1]

Still isnt working…

[WillowMobileSys]

Hide “Instructions Key”

[Tiger1]

ok. I did. But i am still not seeing instructions - DATA in my view. It has the column [Instructions], but there are no instructions data (values) in the row.

Does this look correct?

[WillowMobileSys]

You obviously needed to change the LOOKUP() to include the actual table names and such. Double check for syntax or spelling errors. If you post the actual function here I’ll look at it too!

[Tiger1]

I also wanted to show you - i have some test data:

[WillowMobileSys]

This last bit you just posted helped. I think the issue is that your Enum values are UPPER case but in Table B they are not. In AppSheet, names are case-INsensitive but actual values in text, I think, ARE case sensitive.

Try changing the Table B “Negative” and “Positive” to UPPER case.

[Tiger1]

Ok I tried that - it didnt seem to do anything…

[WillowMobileSys]

Make sure there are no extra spaces in those cells.

I am totally confused then. This couldn’t be simpler.

Could you create a quick Table view against that Instructions table to see if you can view the values in an AppSheet view at all?

[Tiger1]

I had built an instruction view - to check - and it is all there…

I also checked the space - all good there too…

[WillowMobileSys · Accepted solution]

Remember, for rows already entered, you will need to go into that row in Edit mode and re-save in order for the Lookup() to trigger again.

If that’s not the problem then I wonder if you might be hitting a character limit? I believe I read some murmurs about that in posts.

Save a copy of the Instructions columns from that table.

Then in the actual Instructions column delete but a couple sentences of the text. Then see if that shows up.

[Tiger1]

ok thanks

[Tiger1]

You were exactly right. That old data wasn’t applying my new changes. It works now! Thank you very much!