I am trying to build a form that will allow t...

[Peter_Teo]

I am trying to build a form that will allow the issue of a gift if a client’s ID and a flag is set in the client spreadsheet each day. A separate usage spreadsheet will record the client ID, and Date the gift was issued. In short I want the staff to enter the client ID in the App. If the client is eligible, his/her details will appear in selected fields, the form will allow the client to sign for the gift. The app should not allow

a client to receive more than one gift a day.

Slices appear to only allow predefined filters, forms only allow entry of new data. I don’t know how to create a slice that allows the app user to search for the right row in the client spreadsheet in the app. Display data from the client spreadsheet and also populate a usage spreadsheet.

Can anyone help? Thanks

[Aleksi_Alkio]

Did I understand your requests correctly… you want to add a client’s gift and read the data from another table for the form. If that client already has a gift for today, the user can’t add a record for that client. Is this correct?

[Peter_Teo]

@Aleksi_Alkio Hi Aleksi. We run a program where we give out free breakfast and dinner meal coupons to those in need. These coupons can be used in exchange for a meal at neighborhood restaurants. The whole program is done manually at the moment. I am trying to help automate it. Each client will receive a card with a unique ID. When the client requests a coupon, the volunteer will scan the card and some details about the client should appear. We want to know if there has been a change in some of his/her key circumstances to help us provide assistance. That part I think i could do with “lookup.” I haven’t tried it yet. The second part is to check if the card has been used for that meal on that day before. Thats where I think I am stuck. The table that records receipt of the coupon will have a column with the unique ID repeated multiple times because the client will have received a coupon for many days. A Look up on the ID will generate many dates and some dates will be repeated twice because he/she opted for a breakfast and dinner coupon. I could break it into 2 tables, breakfast and dinner but still there is the multiple dates. The 3rd part of the form would be the signature and today’s date. I have not figured it out but I think that should be easy.

[Aleksi_Alkio]

For the 1st part… yes you can read the data with the LOOKUP expression. For the 2nd one… you could check with suitable Valid_If if that user is already use this day’s coupon or not. You could read it like {Breakfast,Dinner} - SELECT(Table[Coupon],[Date]=TODAY())

[Peter_Teo]

@Aleksi_Alkio Thanks for the super quick assistance. I’ll give your suggestions a try.

[Peter_Teo]

Hi Alkio. I’m stuck with the LOOKUP expression and tearing my hair out because its critical to everything else I need to do.

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

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? The formula I used comes from appsheet.com - The no-code app maker for Google Sheets, Excel, and more. Create mobile iPhone and Android apps for your business. Start for free and build powerful apps. - 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’)))

Reading through past posting in the community, someone said that Any(Select…) was the way to go and Lookup just returns the same value so it does not work. Being able to scan or type in a code and retrieve a client’s details is essential to everything I need to do. I have tired Dereference. It works but it uses a pull down menu and we have just too many clients to use this method. Thanks very much for your help.

The no-code app maker for Google Sheets, Excel, and more. Create mobile iPhone and Android apps for your business. Start for free and build powerful apps. appsheet.com

[Peter_Teo]

@Peter_Teo Hi Alkio. Someone helped me. I used Dereference and turned scanning on. Dumb me. Would still like to use Lookup for multiple reference tables but thats a problem for tomorrow. Thanks

[Aleksi_Alkio]

Your formula

LOOKUP([_THISROW].[FIN],Client_Details,FIN,Client_Name) should work correctly. If it doesn’t show any value, it normally means that it doesn’t find any value… value is blank.