Hello, I have a customer table, an installati...

ux
(Rémi Garson) #1

Hello, I have a customer table, an installation table, with several installations per client. My sav form asks me for the client name and I want the following field to be an enumlist of the installations in reference to the client Is it possible ? how?

Thank you

Thank you

(Tony Fader) #2

@Remi_Garson Use a valid_if to create a dropdown help.appsheet.com - Dropdown from Valid_If Dropdown from Valid_If help.appsheet.com

(Rémi Garson) #3

I forgot to specify the entry name refers to a table and the entry instalation refers to another table see the attached drawing table sav

(Rémi Garson) #4

@tony

wow !! it works ! thank you for your help, and for getting me by myself! I understood new things today.

(Rémi Garson) #5

@tony sorry again a little trouble, I was expecting to have a line here:

(Rémi Garson) #6

and I have an exclamation point here

(Tony Fader) #7

The exclamation point means the reference is invalid. The values of a ref column should be key values from the referenced table. Your other problem is probably another manifestation of having an invalid ref value.

(Rémi Garson) #8

@tony

So I create a virtual column ref that should send me the key of the installation table according to the previous 2 conditions: = SELECT (installation [installation ID], and ([name] = [_ THISROW]. [Client], [type] = [_ THISROW]. [Type]))

this formula returns a list and is therefore not compatible with a Ref column whereas a single value can be returned in principle

(Tony Fader) #9

@Remi_Garson If you know that only a single value will match, you can wrap your select expression in ANY:

ANY(SELECT(…))

(Tony Fader) #10

@Remi_Garson You might also be interested in the LOOKUP function, which is just shorthand for ANY(SELECT(…)).

appsheet.com - Lookup Function - How to use the LOOKUP function Lookup Function - How to use the LOOKUP function appsheet.com

(Rémi Garson) #11

@tony

It works but this formula does not work with a ref column, I worked around the problem with a column text [test] then a column ref [test suite] = [test] Is there another best method?

(Tony Fader) #12

@Remi_Garson Can you change your essai column to be a Ref pointing to the correct table?

(Rémi Garson) #13

Thank you, it works! I had to make a mistake when assigning the table

(Tony Fader) #14

@Remi_Garson The usual way to have an EnumList populated by values from another table is with a valid_if. Can you explain why that won’t work in your case?

(Rémi Garson) #15

@tony

it will also work with a real column instead of the virtual What is the preferred method?

(Tony Fader) #16

@Remi_Garson It will work either way. Virtual columns’ formulas are always recomputed. Non-virtual columns’ formulas are only computed when the row itself changes and are written to your sheet. There’s no preferred method, since it all depends on your needs.

(Rémi Garson) #17

@tony ok, I’ll think about it thanks for the help provided

(Rémi Garson) #18
(Rémi Garson) #19
(Tony Fader) #20

@Remi_Garson Your formula is not using the correct syntax. Please look at the third example in the article that I linked to.