I am trying to pull in details of a client in...

(Peter Teo) #1

I am trying to pull in details of a client in row based on the code entered by the user using the app’s scan function. I have tried using “dereference.” that works well but it relies on the use of “ref” which means a pull down menu. There are too many rows to use a pull down menu and the user will not be able to remember the code. “Lookup”

may be the way to do this. Are there alternatives?

(Peter Teo) #2

My Lookup Function is not working? I have

Two Tables -

“Client_Details” with FIN, Client_Name and other columns (containing client particulars) The search table called “Entitlement" with FIN, Name columns and other additional columns

In the Entitlement field FIN is TEXT and Scannable

In the Name Column Definition of the Entitlement table I used the LOOKUP formula. LOOKUP([FIN], “Client_Details”, “FIN”, "Client_Name”) FIN is a key in the Client_Details Table but not in the Entitlement table

The current result is Name field in the form linked to the Entitlement table always displays the first row Client Name in the Client_Details Table. Is my use of the lookup expression wrong?


(Peter Teo) #3

The formula I used comes from appsheet.com - Lookup Function - How to use the LOOKUP function I also tired LOOKUP([_THISROW].[FIN], “Client_Details”, “FIN”, "Client_Name”) the result was the Name field does not show up at all in the form. Both formulas pass the verification test. and produces this result.

One randomly chosen value from this list ( …The list of values of column ‘Client_name’ …from rows of table ‘Client_Details’ …where this condition is true: ((The value of column ‘FIN’) is equal to (The value of column ‘FIN’))) Lookup Function - How to use the LOOKUP function appsheet.com

(Peter Teo) #4

@Suvrutt_Gurjar we provide free assistance to migrant workers with legal issues. There will be a table to track unpaid salary problems another with injury compensation claims another with civil claims and another with criminal charges. The summary table will need to pull information from all 4 tables.

(Suvrutt Gurjar) #5

Hi @Peter_Teo Thank you for update. If the references are for simply pulling data from other 4 tables, it could be possible, even though I have myself not tried it at large level. Just tested it on a prototype with minimal data and columns. Also if there are other business inter dependencies between referenced tables ,then I believe it is difficult to comment.

There is an example of references to 2 tables in the attached sample app.Please refer to table “Order details”.

Hope this helps in some way.

appsheet.com - Order Capture - An app for managing customers, products, and orders.

Order Capture - An app for managing customers, products, and orders. appsheet.com

(Suvrutt Gurjar) #6

Hi @Peter_Teo, Is it correct to assume that

  1. Entitlement table references Client_details table. FIN is key in Client_details table.

2)FIN is also the reference column and scannable but not key in Entitlement table.

  1. You are pulling in information from Client_detail into Entitlement table through FIN key column
(Peter Teo) #7

@Suvrutt_Gurjar hi Suvriutt. Your assumptions are correct. Thanks for responding

(Peter Teo) #8

Hi Suvrutt.

I am not so worried about scanning bar codes. I’ve tested it Nd it works well. The problem is populating the form (entitlement) table with information from the “client details table. I can enter the code manually and nothing happens. Most of the examples of lookup use drop down menus or enum except for the link in my post and that does not seem to work.

(Suvrutt Gurjar) #9

Hi @Peter_Teo, If I understand requirement correctly, I believe that is what the shared app does.

The scanned item ,after a save gets the data from Itemlist into Scan Items table.

Please see menu views for Itemlist and Scanned


(Suvrutt Gurjar) #10

Hi @Peter_Teo,Just to add, in the sample app also you can simply scan an item by bar code. Presently drop down list is there with pseudo bar codes so that app can be shared as a public app.

Please enter details of an item whose bar code number you know into itemlist table along with other columns such as description etc in the back end. If you scan this item through the app on phone, it will pick up the item details from Itemlist table and port them to Scanned items table.

Hope this helps.

(Peter Teo) #11

@Suvrutt_Gurjar Hi Suvrutt. Thanks. What I missed was “ref” fields could also be scannable. This is great when the form references just one reference table. How would you do this if you have to reference 2 tables? Lookup was a generic expression that seem to allow access to information from multiple tables. Does it work?

(Suvrutt Gurjar) #12

Hi @Peter_Teo, May we know what the second reference table is for?

(Suvrutt Gurjar) #13

Hi @Peter_Teo,You may wish to take a look at following sample app for concept of pulling scanned item data

from Item list table into a scanned items table.

Please take a look at the app 'Repeat Scans n Save" at