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

Filtering on multiple coloumns in SQL

Hi Guys, I have a field called "location" as combobox in my form and I am using my sql table as the datasource for this (Attached picture_). In my table I have few coloums such as "BuildingName", "SiteSubType" and "District". So I want to filter the SiteSubType coloums as DataCenter and get the "BuildingName" and "District". I tried the below syntax: Distinct(Filter(SortByColumns('[dbo].[SP_Hierarchy]',"BuildingName",Ascending),SiteSubType= "Data Center", Status = "Active"), BuildingName) but I only get the Building Name. And then I tried this: Distinct(Substitute(Filter('[dbo].[SP_Hierarchy]', SiteSubType = "Data Center", Status = "Active").District,"Leased Data Centers",""),District) but I only get District How can I combine these two? Any help is much appreciated!! thanks a lot
Community Support Team Rank
Community Support Team

Re: Filtering on multiple coloumns in SQL

Hi @asdeev,

Could you please share a bit more about your scenario?

Do you want to filter your data source ('[dbo].[SP_Hierarchy]') based on SiteSubType column, and get the "BuildingName" and "District"?

Further, do you also want to filter out the records whose District column value is "Leased Data Centers"?

Based on the formula that you mentioned, I think the Distinct function could not achieve your needs. The Distinct function is used to return a one-column table that contains the results, with duplicate values removed.

More details about the Distinct function, please check the following article:

Distinct function

Based on the needs that you mentioned, I think the ShowColumns function or GroupBy function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

ShowColumns(
Filter(
SortByColumns('[dbo].[SP_Hierarchy]',"BuildingName",Ascending),
SiteSubType= "Data Center",
Status = "Active",
District <> "Leased Data Centers"
),
"BuildingName",
"District"
)

In addition, you could also consider take a try with the following workaround:

GroupBy(
Filter(
SortByColumns('[dbo].[SP_Hierarchy]',"BuildingName",Ascending),
SiteSubType = "Data Center",
Status = "Active",
District <> "Leased Data Centers"
),
"BuildingName", /* <-- ColumnName */
"District", /* <-- ColumnName */
"GroupData"
)

More details about the GroupBy function and ShowColumns function, please check the following article:

GroupBy function

ShowColumns function

 

Best regards,

Kris

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.
asdeev
Level: Powered On

Re: Filtering on multiple coloumns in SQL

Yes, I want to filter the data source ('[dbo].[SP_Hierarchy]') based on SiteSubType column, and get the "BuildingName" and "District"?

Yes, I  want to filter out the records whose District column value is "Leased Data Centers". 

 

It works fine thanks so much for your help. What I did was in the patch function I set the location value as : Location:Concatenate(ComboBox2.Selected.BuildingName, ComboBox2.Selected.District)

 

But now to get the selected value of location automatically in the edit form view. . I kept the Update value of the location card to Update: ComboBox2.Selected.BuildingName && ComboBox2.Selected.District

and the default: ThisItem.Location ( these are for the card itself)

 

In my combox :

Default value of the combox2 is set as Default: { Result: Parent.Default}

DefaultSelectedItems of my combox2 is empty since I have set the "SelectMultiple field of combobox to false.

 

This isnt working. I still dont see any location when I re-open the submitted form to edit. It shows blank. I am not sure what I am doing wrong!!

 

 Please guide.

I have attached the pictures for reference.

 

Thanks alot!!

asdeev
Level: Powered On

Re: Filtering on multiple coloumns in SQL

Actually my apologies,

 

The formula you mentioned with regards to groupby and showcoloumns() doesnt work. I basically want 2 coloums from sql to show in one "location"combobox in my powerapps. 

 

Let me explain my scenario: Lets say the below coloumns are in my database table [SP_Hierarchy]

BuldingName

1. Dublin 1

2.Dublin 2

3. Dublin 3

4. Chicago1

5. Chicago2

 

District

1. Dublin corp data center

2.Dublin corp data center

3.Dublin corp data center

4. Chicago Northeast

5.Chicago Data Center

 

 

Site SubType

1. Data Center

2. Data Center

3. Data Center

4.KIosk

5. Data Center

 

In my Powerapps I have a combobox called "location"

so I want to have the District and BuildingName of all Datacenters in my Location combobox.  So I want to filter SiteSubType= Datacenter and get the buildingName and District.

 

I hope that makes sense!!