Solved: Dependent Drop Down List 2 level

Leo-Enoch · 06-26-2020 09:51 AM

Hi everyone,

I was trying to do a 2 level dependent drop down list, but could not get it working.

Example:

I have list of Bank Name Drop down then then list of Account No and Then list of FX.

In first list to choose, I place in Bank Name Column this validif = Bank[Bank Name]
Then in Account No Column validif = Bank[Account No]

and now from these account no, I need to choose drop down list of all the currencies

so on Currencies Column I place validif = Bank[FX].

I manage to get the dependent drop down to Account No, but not to the Currency.

I am not sure where I make the mistake is.

Thanks

LeventK

Your shall try with

SELECT(
    Bank Pogen[FX],
    AND(
        [_THISROW].[Bank] = [Bank],
        [_THISROW].[Account No] = [Account No]
    )
)

View solution in original post

Steve

Leo-Enoch

Hi Steve,

I tried to follow that example, yet it is not working. here let me share in detail:

my Bank Detail table is as follows:

On the FX field, I used enum, so user can enter USD or SGD.

Then I have a transaction table for transaction entry, and I want the user to choose the Bank, then the account No and from the same account no choose the currency.

On the Apps look something like this, I can get it to work dependent drop, when I choose bank, it only shows the related account no, but on fx it just not showing the 2 currency under the account no:

On the Bank Column in transaction table validif = Bank Pogen[Bank] - ok working
On the Account No column in Transaction table validif = Bank Pogen[Account No] - ok working

On Fx Column in transaction table validif I tried the followings:

= Bank Pogen[fx] - Not working- Not showing fx choice

= IN( [_THIS], SELECT(Bank Pogen[FX], [_THISROW].[Bank] = [Account No])) - not working- Not showing fx choice

= SELECT(Bank Pogen[FX], true). - not working (showing fx choice but for all condition)

=SELECT( Bank Pogen[FX],([_THISROW].[Bank] = [Account No])) - Not working - Not showing fx choice.

Appreciate your help on the above…

LeventK

Your shall try with

SELECT(
    Bank Pogen[FX],
    AND(
        [_THISROW].[Bank] = [Bank],
        [_THISROW].[Account No] = [Account No]
    )
)

Leo-Enoch

Thanks @LeventK, I tried this expression, but somehow resulted in the column is missing.

But when I checked the expression of the column by itself without the Select (), the column is there. Bank Pogen is a slice of the table Bank Detail. So I believe it should be correct Table[Column] format. on the first and second drop down it worked.

LeventK

@Leo_Hermanto
It’s not working because your expression syntax is not correct and completely wrong. You don’t need to specify the tablename in front of the columns. Try with the expression that I have given wo/changing it.

LeventK

@Leo_Hermanto
Please refer to my note above…

Bank Pogen[Bank ID] is already a list and [_THISROW].[Bank ID] returns a text value. So you are trying to compare a text against a list as the error indicates.
[_THISROW].[Bank ID] = [Bank ID] is text vs. text comparison and returns a TRUE/FALSE value which is the correct parameter of the SELECT function.

Leo-Enoch

Hi @LeventK

Thanks for highlighting, I guess I am still confused with the Logic. and maybe I did not detail out clearly what I would like to achieve. I tried to rewrite the valid if as per your suggestion below, but it is not giving me the desire result.

SELECT(
Bank Pogen[Bank],
[_THISROW].[Bank_ID] = [Bank_ID],
)

Let me try to detail out the results I would like to achieve.

Transaction can be entered by Company, by Person or By Bank - Category…
So if you see the transaction table there are Company ID that will be generated should the transaction is entered through the inline form view from Company.

The same also happen for Person ID that will be filled automatically when I entered transaction from the inline form view from Person View. The earlier dependent drop down works fine when you enter transaction from these 2 menu, Company and Individual.

But then if you enter the transaction inline from the Bank Menu, where you have already chosen the bank first before the transaction, then the dependent drop down is not correct. so I need to be able to pre choose the Bank Name and account no and fx according to the Bank ID that has been generated

Here are the table involve in the process:

Transaction Table: As you can see the ID is generated from ref at the parent table. as transaction is entered thru the inline form related to the parent data.

Bank Detail Table, Bank Pogen is a slice of Bank Detail.

The problem happen when the transaction is entered from the Bank Menu… It should not allow the user to enter any other bank a/c except the one where it was selected from.

Leo-Enoch

@LeventK

I have solved this issue, by using dereference on the column combined with setting of Initial Value and Editable_if ISBLANK(Bank_ID)

Thanks for jotting my creativity…

Leo-Enoch

Thank you so much… It work wonders

LeventK

You’re welcome

Leo-Enoch

Hi @LeventK

I try to develop this fuctionality. by pre choosing the drop down list of the bank name only when the Bank ID in transaction table matches the Bank ID in Bank Pogen Table. however the result I receive is not matching as cannot compare text with list

Is there a way to return only the value bank name that matches the bank ID or as in previous problem only to return one FX value, SGD or USD based on the matched Bank ID in the transaction table with the Bank Pogen Table.

Is this the right approach?