Power Platform Community Forum Thread Details

I am working on a Power Automate flow that uses an OData style filter expression to filter a Dataverse table and return the results in a CSV file. The filter parameters are selected in a Canvas App and when the user selects the "Export" button, it should export to a CSV through a Power Automate flow. I have three tables in my solution:

States and Regions
- State Abbreviation (Primary)
Contracts
- Contract Name (Primary)
- State Abbreviation (Lookup to States and Regions)
On Contract Pricing
- Contract Name (Lookup to Contracts)
- State Abbreviation
  - This is an fx column type that pulls the State Abbreviation from the Contracts table.
  - The fx expression for the column is 'Contract Name'.State.'State Abbreviation'

The user has the option to filter by either: State Abbreviation or Contract Name. I have figured out the OData filter syntax for the "Contract Name" column which is:

pre_ContractName/pre_contractname eq 'INPUT CONTRACT NAME'

However I can't seem to figure out the syntax to filter for the State Abbreviation. I believe this has to do with the column type being a formula/fx column. I've tried many options including:

_pre_stateabbreviation_value eq "INPUT STATE ABBREVIATION"

_pre_StateAbbreviation_value eq "INSERT STATE ABBREVIATION"

_pre_stateabbreviation_value/njs_stateabbreviation eq "INSERT STATE ABBREVIATION"

The column name on my "On Contract Pricing" table is "pre_stateabbreviation". When I look at the output from a successful flow using the Contract Name, I can see that the State Abbreviation column output looks different than the Lookup column types.

LOOKUP COLUMN OUTPUT:

 "_pre_contractname_value@OData.Community.Display.V1.FormattedValue": "Example City",
 "_pre_contractname_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "pre_ContractName",
 "_pre_contractname_value@Microsoft.Dynamics.CRM.lookuplogicalname": "pre_contracts",
 "_pre_contractname_value@odata.type": "#Guid",
 "_pre_contractname_value": "48fbf5d1-fde6-ee11-904d-000d3a5c4b80",

fx/FORMULA COLUMN OUTPUT:

 "@odata.type": "#Microsoft.Dynamics.CRM.pre_oncontractpricing",
 "@odata.id": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/pre_oncontractpricings(8b4ac085-b8e7-ee11-904d-000d3a5c4875)",
 "@odata.etag": "W/\"91734574\"",
 "@odata.editLink": "pre_oncontractpricings(8b4ax085-b8e7-ef11-904d-000d3a5c5175)",
 "pre_stateabbreviation": "TX",

So my question is - how the heck do I write my OData filter syntax for the formula/fx column type?

Fingers crossed!

Categories:

Building flows