ID column for the same form but in different tables

[Aditya_Vinayak_]

I have a few tables:

1) Customer Table >> Under which there is Pet Details Table & Packages Table >> Under Pet Details Table, I have service booking table & the Packages table also I have linked with Service Booking Table.

When I go within a pet & create a booking the Pet ID automatically gets filled & assigns it correctly.

But when I go to the packages table & try to book a service, In the Pet ID column it shows me all the Pet IDS & not for that specific customer. 

How can I make sure it only shows me the Pet ID for that specific customer?

[Aditya_Vinayak_]

@Suvrutt_Gurjar can you please help on this..

[assessordaf]

Vá para a edição de colunas, click na coluna ID para que se abrar o menu de configurações da coluna.
Click escolha o tipo de coluna para Texto label, escolho a opção de texto inicial (uniqueid), salve o projeto e faça o teste

[Aditya_Vinayak_]

I tried translating & understanding your response.

But doesn't look like it will help,

[Aditya_Vinayak_]

@AleksiAlkio Please assist on this.

[Suvrutt_Gurjar]

Could you mention your requirement with a few screenshots? How tables are related and in which view you would like the requirement you have stated needs to be implemented.

May I request you to refer the posts below as sample posts for stating the requirement when the requirement is complex involving multiple tables etc. That will help to get more precise and faster solutions.

Solved: Teach me how to display measure_time value from an... - Google Cloud Community

Solved: Issue with EnumList Dropdown of Criteria - Google Cloud Community

[Aditya_Vinayak_]

GOAL:

1) The "Pet ID" column in service booking form under the "Related Package" table should show only the pets belonging to that particular customer.

Right now what the field shows is blank.

Current formula is:

Just to explain the relationship:

Customer Table >> Pet Table >> Service Booking >>Service Availed >> Payments Table

[Aditya_Vinayak_]

Customer Table>> Packages>>Service Booking

[Suvrutt_Gurjar]

Thank you. It would have been better if you had given column details as  well. As mentioned, for such intricate reference relationships, the solution will depend on certain specific expressions which in turn are based on column names.

Anyway, please try below.

1) Please add a VC called say [Pet_Name] in the table Packages with an expression something like 

INDEX([Customer_ID].[Related Pet_Details],1)

The type of this column Ref and referenced table name "Pet Details"

Where [Customer_ID] is the reference column in the Packages table that references Customer table and [Related Pet Details] is the reverse reference column in the Customer table related to to the "Pet Details" table.

2) In the initial_value of column [Pet ID] in the "Service Booking" table that references the Pet Details table, please add an expression something like 

[Package_ID].[Pet_Name]

where [Package_ID] is the refer column name in the "Service V=Booking" table that references the "Package" table and [Pet_Name] is the VC created in step 1 above.

[Aditya_Vinayak_]

Do you think instead of Index, something else has to be used which gives me a list of the pets under that particular customer?

[Suvrutt_Gurjar]

If you have [ pet id] as reference then it will not be possible. Because reference will expect a singular value as the input.

You could alternatively try enumlist column with base as reference for multiple values but in that case the reverse reference etc, is not created automatically. Also pulling any parent table specific values through dereference etc. will be an issue.

Please search for posts with keywords such as :enumlist with base type as reference" to understand related configuration and issues.

Many-to-Many Relationships - Google Cloud Community

Search - Google Cloud Community

If you do not wish referencing relationship between Service Booking and Package then one can easily pull multiple names but you may have other complications due to not using references.

You may want to weigh in pros and cons of these options.

[Aditya_Vinayak_]

@Suvrutt_Gurjar This does give me the first pet name.

But in cases where there are 2 pets, can I restrict the list to only the pets which belong to that particular customer. Because right now it is showing me all the pets if i try to edit the field.

[Suvrutt_Gurjar]

Could you update what kind of that column Pt_ID is? What are those  dash separated names ("-" )

Meaning why there are multiple names in the single dropdown field?  Are those pets of the same customer?  How they appear separated by dash?

[Aditya_Vinayak_]

So Pet ID is a Ref column actually. 

In the pet details table I have created a VC by the name of detailed name, which is a concatenate of multiple fields in that table. And it has been made the label.

That is why in the pet id being a ref column it shows the label for easy identification.

Does that answer the question?

[Suvrutt_Gurjar]

Could you update what that concatenation is? Are these the pets of the same customer? If so, how do they appear in the same Pet Details record - one pet per record or can a Pet Details record have multiple pets?

Again I may say that a screenshot of relevant column names and their types in all related tables will help for a better and faster solution. A community member may not know such intricacies ( for example concatenation of pet names) without such details. 

[Aditya_Vinayak_]

Concatenation Formula: Concatenate([Customer ID].[Customer Name]," - ",[Pet Name]," - ",[Breed])

The above formula is applied on the Pet Details Table, in the Detailed Name column.

So I have made it in such a way that, Under one customer you can add multiple pets. So under the pet details table all the pets are recorded but they are linked with the respective customer through Unique IDs.

[Suvrutt_Gurjar]

Thank you. If the three fields are concatenated as label, then I am afraid those will appear as label. You may want to consider mere [pet Name] as label in that case. Currently AppSheet does not allow two different labels for the same table at two different places. And in case of a referenced column the column marked as label is displayed in the app even though the backend table stores the reference column.

So depending on use case, you may want to consider either a simple one column based label or concatenated label as the case may be.

[Aditya_Vinayak_]

But why does the dropdown show all the pets rather than only the pets which are under that particular customer?

[Aditya_Vinayak_]

Screenshot of the Service booking table