Editable Form & Gallery Filtering

[carrie-anne27]

Hi all,

I need your help desperately!

I have two tables. (These are Dataverse tables I that is important)

Table 1: New Project

Table 2: Catchments & Assets

In my blank canvas app, called: New Project Creation Tool, I need some type of filter to achieve what I need to do, but I am stumped. (I am creating this app for the company if work for)

In the app I have a Screen called scr_Job_Number_Creation, in that Screen I have a Form, Service_Type_Form: Service_Type_DCV (DataCardValue - Combo Box), this is connected to Table 1: New Project, the Service_Type_DCV Items is set to Choices('Service Type (New Projects)'), this means I can choose between:

1. Water
2. Sewerage
3. Recycled Water
4. Support Services

The column in Table 1: New Project is a Choice column.

Also, in this Screen I have a Gallery gl_Catchment, this Gallery uses data from Table 2: Catchments & Assets, the Gallery has a Combo Box in it, cmb_Catchment and is connected to a choice column called “Catchment”, this column lists various sites, some are Water – Sewerage – Recycled Water – Support Services sites.

What I need:

When a choice from Service_Type_DCV is selected, it only brings up the matching Catchment sites in cmb_Catchment.

For Example:

If I select Service Type: “Sewerage” from the choice column in Table 1: New Projects, then only Catchments that are Sewerage appear from Table 2: Catchments & Assets in cmb_Catchment.

I need this for not only Sewerage Catchments but also, Water, Recycled Water & Support Services

If you need more information, please ask.

I am desperate as I only have a limited amount of time to complete the app.

[Amik]

@carrie-anne27 -

As I understand it.

You have two Dataverse tables. Table 1 (New Project) and Table 2 (Catchments & Assets).

Table 1 includes a Choice column ("Service Type"). Looking at your Choices syntax, it is a Local Choice column scoped to Table 1.

Table 2 also includes a Choice column ("Catchment"). I am going to assume it is also a Local Choice column.

In your App screen, you have an Edit Form control. The Datasource property for this Form is bound to Table 1. This Form includes a DataCard for the "Service Type" Choice column.

On the same screen, you have a Gallery control (I will call it Gallery1). The Items property for Gallery1 is bound to Table 2.

You have embedded a ComboBox control inside Gallery1. The Items property for this control is the "Catchment" Choice field.

You want to Filter the Catchment ComboBox control to display only Choice Values which include a word which exists in the selected Choice from the Service Type DataCard in the Edit Form.

Is that correct?

[carrie-anne27]

Hi Amik,

Yes that is correct. If there are different ways I should set out my tables to make it work I am happy to do that, I am also happy to change my inputs in the app

[Amik · Accepted solution]

@carrie-anne27 - try applying the below onto the Items property of the embedded ComboBox:

Filter(
    AddColumns(
        Choices('Catchment (Catchments & Assets)'), //I have guessed the naming here
        "_TextChoice",
        Text(Value)
    ),
    Service_Type_DCV.Selected.Value in _TextChoice
)

[carrie-anne27]

By Embedded Combo Box, do you mean the one in the Gallery?

[Amik]

@carrie-anne27  - yes - the one you have named "cmb_Catchment".

[carrie-anne27]

Sorry to be a pain but I am not really understanding the highlighted parts below 

Filter(
AddColumns(
Choices('Catchment (Catchments & Assets)'), //I have guessed the naming here
"_TextChoice",
Text(Value)
),
Service_Type_DCV.Selected.Value in _TextChoice
)

[carrie-anne27]

@Amik 

If it helps I also have a column called "Catchment ID" this lists the corresponding numbers to the service type sites.

See how 2 = Water & 3 = Sewerage 

[Amik]

@carrie-anne27 -

Regarding my comment "I have guessed the naming here". Although you provided the syntax for your "Service Type" Choice field, you did not provide the syntax for your Catchment field.

Please replace this line:

Choices('Catchment (Catchments & Assets)')

With the actual syntax for the Catchment Choice field. This will be the name of the field in your table ("Catchment") together the Plural name of your table.

For example, here is the syntax for your "Service Type" Choice field:

Choices('Service Type (New Projects)'),

We can see above that the name of the field is "Service Type", and the plural name of your New Project table is "New Projects"

If you're struggling with understanding the syntax, then simply create another Edit Form on a separate screen, set the DataSource property to your Catchments & Assets table, add the DataCard for the Catchment field, then select the automatically generated ComboBox inside the DataCard, and copy whatever you see in the Items property.

Regarding "_TextChoice", please leave that as is.

[carrie-anne27]

AMAZING!! Thank you so much for your help!!!