Autofill not filling the fields

Manish_Jain11 · 02-25-2021 07:42 AM

I have 2 Tables - Orders and Customer

Order Table has fields - Customer and Customer ID

What I am trying to do is In Orders Form ( when creating or editing the order) I want to autofill the Customer ID (referring the customers table) upon the selection of the Customer . I tried the LOOKUP but its not working the way it should. Its showing me the customer (name) in the customer ID field too instead of customer ID. And when I select the customer from the drop down, the entire Customer ID field is getting disappeared from the Order Form .

This is what I have used but doesnt seem working.

LOOKUP( [_THISROW].[Customer] , Customers , Customer , Customer_ID )

Where am I going wrong ?

MultiTech

First: change the post to a question - since this isn’t a tip on how to do something.

Second: you’ll want to check out references:

They’ll allow you to connect things together and do what you’re wanting automagically.

Hope it helps!

Manish_Jain11

I also Changed the Customer ID column type from Ref to Text. It seems to have solved a bit but not completely. On Changing the customer name, the customer name field is getting filled with customer ID Value.

MultiTech

How are you getting that value?

With a reference, you should de-reference it

Manish_Jain11

Hello. I did exactly as in the article and even the video along. Its doing the right reference now but the other problem still remains… populating (changing) the Customer Name with Customer ID .

Steve

That would use a dereference expression lilke this:

[Customer ID].[Name]

replacing Name with whatever the name of the column is in the customers table that contains the customer’s name.

Manish_Jain11

Actually the dereference problem is sorted I think. Upon the customer (name) selection, the ID is getting stored in the customer ID Column so far so good. But it stores the same customer ID in the customer(name) field too. It shows the customer (name) in the dropdown but stores as ID in the field. Kindly refer the image in my previous post.

Steve

I stand by my suggestion above.

Manish_Jain11

Where should I have this expression . In the customer (name) Field which is of Type Ref. This is what I have currently

In the Order Table

Steve

Oh, Customer is the Ref. I suggest you reverse them. Make Customer ID the Ref and fill in Customer with the customer’s name.

Manish_Jain11

Works .This was the first thing I did but got the list of customer IDs in dropdown instead of the name, should have made some mistake. So reversed the ref to customer name field to make the selection easy but it got worse. Anyway, it works at last. Thanks Steve.

While my problem is solved and is all fine, I am still confused as to how the dropdown automatically lists the customer names and not the other columns from the customer table like the City or others. Is this preconfigured or some intelligence applied ?

I am trying to understand this because I want to populate the dropdown values according to my wish in other views which I am working on.

Steve

When a column is of type Ref (or List of Ref), the actual column value is the key column value of the row of the referenced table, but AppSheet displays the label column value of the row. This is built-in behavior for Ref values.