Two-way Sync Between Account ID and Account Name

[Boray]

Hi everyone,
In my app, the Opportunity data is defined as a child table of Account, and it’s linked to the Account table through a reference column using Account ID. When users create a new row in the Opportunity table, they select an Account Name, and based on that selection, the related Account ID is fetched as an initial value from another table.

However, when I’m in the Account detail view and click the Add button under the Opportunity section, the Account ID field is already populated, but the Account Name field remains empty.

Is it possible to have the Account Name automatically filled in as well in this case?

In other words, is it possible to establish a two-way sync between Account ID and Account Name? For example, if Account ID is selected, Account Name should be automatically retrieved, and vice versa—if Account Name is selected, the corresponding Account ID should also be filled in automatically.

[Boray]

Isn’t there a turnaround for this? On page A, the account id value should come with the formula ANY(SELECT(account[account id], [account name] = [_THISROW].[account name])), and on page B, the account id should have a formula, and the account name value should come with the formula ANY(SELECT(account[account name], [account id] = [_THISROW].[account id])), something like that.

[Aurelien]

Hi @Boray 

In your Account table, you may want to use a virtual column that will concatenate both Account ID and Account name.

For example:

[account_ID] & " - " [account_name]

 For reference:

Use virtual columns - AppSheet Help

Add row labels - AppSheet Help

[Boray]

Hey Aurelien, thank you for your answer, but how will this help?

[yasarcan]

Hİ select(TABLE[NAME],[_THISROW].[ID]=[REF ID]) GİBİ..

[Boray]

hayır burada durum şu, opportunity tablosunda account id initial value normalde any(select(account[account id], [account name] = [_thisrow].[account name])) formulü ile kullanıcının girdiği account name sayesinde hesaplanıyor. fakat opportunity account'ın child table'ı (account id üzerinden bağlı), account view'unda opportunity kısmında add butonuna bastığımda açılan form child table olduğu için account id kısmı dolu olarak geliyor fakat account name kısmı boş kalıyor. yani bu ekrandan opportunity oluştururken account name değerinin bu sayfaya özel any(select(account[account name], [account id] = [_thisrow].[account id])) formulü ile hesaplanması lazım

[Aurelien]

Well, you will have the two informations displayed in your second table Opportunities.

Isn't it what you are looking for?

Just in case, here is something that may help approching your case, from my perspective: 

References between tables - AppSheet Help

[Boray]

No, here’s the situation: in the Opportunity table, the account id initial value is normally calculated using the formula any(select(account[account id], [account name] = [_thisrow].[account name])) based on the account name entered by the user.

However, Opportunity is a child table of Account (linked via account id), so when I press the Add button in the Opportunity section of the Account view, the form that opens is for the child table. That means the account id comes pre-filled, but the account name remains empty.

So, when creating an Opportunity from this screen, the account name value needs to be calculated specifically for this page using the formula any(select(account[account name], [account id] = [_thisrow].[account id])).

[Aurelien · Accepted solution]

I got it. 

But again, I think there is a misunderstanding of the benefitw with using references with AppSheet.

Just to make it clear, here is my understanding:

Table ACCOUNT

account_id	account_name	other_properties
account_id_1	account_name_1	x

Table OPPORTUNITY

opportunity_id	account_id	account_name	other_column
opp_id_1	account_id_1	(to be retrieved is account_id is filled)	x
opp_id_2	(to be retrieved if account_name is filled)	account_name_2	y

My point is that you can simply use this:

Table OPPORTUNITY

opportunity_id	account_id	other_column
opp_id_1	account_id_1	x
opp_id_2	account_id_1

This is with defining, in the table ACCOUNT, the Label property on the Account name - or on a calculated column that will combine both id and name.

For reference:

Add row labels - AppSheet Help

However, to simply answer your question:

1) use this in the initial value of account_id:

IFS(
  ISNOTBLANK([account_name], 
  ANY(
    SELECT(ACCOUNT[id], 
     [account_name]=[_THISROW].[account_name]
    )
  )
)

 2) use this in the initial value of account_name:

IFS(
  ISNOTBLANK([account_id], 
  [account_id].[account_name]
)

 For reference:

IFS() - AppSheet Help

[Boray]

Aurelien, thank you so much for your great response, this is how we should use the app. I really appreciate it.