cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vffdd
Level: Powered On

filtered look up multiple tables

Hi

 

I have a filtered dropdown as follows

 

(Distinct(Filter(Responsible,RoleID=txt_view_roleid.Text),Variant).Result)  

 

Works fine and returns exactly the variants I need from the Responsible table in the dropdown. 

 

However I now need another dropdown that looks up and filters from multiple data sources, not just Responsible.       RoleID that it looks up and Variant that it returns are the same column names in each of the the other data sources if that helps.

 

any easy way to do this !! ?  I have researched collections and looked at various other suggestions but can't seem to land on the right one !

 

TIA

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
vffdd
Level: Powered On

Re: filtered look up multiple tables

Thanks for the responses. However I felt it was getting far too complex for what I wanted to do !  so I've decided to go look and see if I can find a different approach to what I wanted to achieve.

8 REPLIES 8
Community Support Team
Community Support Team

Re: filtered look up multiple tables

Hi @vffdd ,

Could you please share a bit more about your scenario?

Do you want to look up and filters from multiple data sources within your another Dropdown box?

 

I assume that you use RoleID column to looks up your multiple data sources, and return Variant column values, is it true?

I have made a test on my side, please take a try with the following workaround:

Set the OnChange property of the txt_view_roleid Text box to following:

ClearCollect(
                DropdownCollection,
                Filter(Responsible, RoleID = txt_view_roleid.Text).Variant,
                Filter(YourAnotherDataSource,  RoleID = txt_view_roleid.Text).Variant   /* <-- YourAnotherDataSource represents your another data source */
)

Set the Items property of the Dropdown box to following:

Distinct(DropdownCollection, Variant)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vffdd
Level: Powered On

Re: filtered look up multiple tables

Thanks Kris, I will try that but not sure it's what I need?

 

What I want to do is look up data (Variant)  in multiple datasources and return them all in one drop down

 

so

(Distinct(Filter(Responsible,RoleID=txt_view_roleid.Text),Variant).Result)  i

 

looks up only the Responsible datasource,  I want to look up 3 other datasources  with exactly the same paramaters.. RoleID=txt_view_roleid.txt  and return the results all in the one drop down 

 

TIA

vffdd
Level: Powered On

Re: filtered look up multiple tables

Anyone got any ideas on this one?  many thanks

SabinSharma
Level: Powered On

Re: filtered look up multiple tables

Here is what i have tried.

 

Step 1: Create DataSources (X and Y) 
Event: App>OnStart

ClearCollect(
    X,
    {
        field1: "valueX",
        Field2: "valueY"
    },
    {
        field1: "valueXX",
        Field2: "valueYY"
    },
    {
        field1: "valueXXX",
        Field2: "valueYYY"
    }
);
ClearCollect(
    Y,
    {
        field1: "value1",
        Field2: "value2"
    },
    {
        field1: "value11",
        Field2: "value22"
    },
    {
        field1: "value111",
        Field2: "value222"
    }
)

Step 2: Create a nested datasource for dropdown

Event: App>OnStart

ClearCollect(
    NewCollection,
    ClearCollect(
        Xcollection,
        ForAll(
            X,
            X[@Field2]
        )
    ),
    ClearCollect(
        YCollection,
        ForAll(
            Y,
            Y[@Field2]
        )
    )
)

Step3: Provide 'New collection' datasource values as Items for DropDown
 

NewCollection.Value

Step 4: Right Click on 'App' and Click Run OnStart.

This is just to show how you can get a field from multiple datasource. You can use filter to filter your data. Hope this works. If it does, Consider marking it as Solution to help other members. 

SabinSharma
Level: Powered On

Re: filtered look up multiple tables

Here it is with the filter.

Step 1: Say you have 2 datasource X and Y with the following fields, "Product","Available" and "Required". You want to get the List of product from datasource A and datasource B that you need to order.

ClearCollect(
    X,
    {
        Product: "ProductX",
        Available: 10,
        Required: 9
    },
    {
        Product: "ProductY",
        Available: 5,
        Required: 10
    },
    {
        Product: "ProductZ",
        Available: 6,
        Required: 9
    }
);
ClearCollect(
    Y,
    {
        Product: "ProductA",
        Available: 2,
        Required: 3
    },
    {
        Product: "ProductB",
        Available: 10,
        Required: 5
    },
    {
        Product: "ProductC",
        Available: 6,
        Required: 4
    }
);
ClearCollect(
    NewCollection,
    ClearCollect(
        Xcollection,
        Filter(
            X,
            X[@Required] > X[@Available]
        ).Product
    ),
    ClearCollect(
        YCollection,
        Filter(
            Y,
            Y[@Required] > Y[@Available]
        ).Product
    )
)

Step 2: Right Click on 'App' and click run on start.
This will generate datasource and the required items for drop box.

Step 3: Insert Dropbox and set it item properties to :

SortByColumns(NewCollection.Product,"Product",Ascending)

Hope this one will work.

vffdd
Level: Powered On

Re: filtered look up multiple tables

Thanks Sabin will try that and let you know

vffdd
Level: Powered On

Re: filtered look up multiple tables

Thanks for the responses. However I felt it was getting far too complex for what I wanted to do !  so I've decided to go look and see if I can find a different approach to what I wanted to achieve.

Highlighted
vffdd
Level: Powered On

Re: filtered look up multiple tables

I am revisithing this because I haven't found a solution or an alternative?

 

summary is  I would like to populate a dropdown box with items from 3 different tables with one common link.

 

So all tables  have  ID# and then item value.   I have an ID# text box and I want to populate the dropdown where the ID# in the textbox matches the ID# in each of the 3 tables  returning the value for each of them .

 

Here's what currently works filtering one table only (Responsible)

 

(Distinct(Filter(Responsible,PL3ID=txt_PL3ID_4.Text),Variant).Result)

 

I want to add in 2 more tables.. but exact same filter..

 

There#s gotta be some straightforward way to this Smiley Happy

 

TIA