Search code examples
datatablespower-automatedynamics-365lookup-tables

PowerAutomate Looking up on a Custom D365 Table

So I have a CloudFlow bringing in the incoming number and using it in the script. That's all working. The issue I'm running into is this: I would like to run a lookup on a custom D365 table. I don't even need to get the variables involved. The issue I'm running into is on the filter.

The column I would like to run a lookup on is a many:1 relationship with the "Phone Number" table in D365. This is what the custom table looks like Custom Table

And this is what my lookup looks like: Lookup Action

The problem I seem to continue running into is the filter clause. I'm going to list what I've tried below. The stars in the text are just there for sanitation purposes, and are actual numbers in the flow. I've tried: cr7b6_num_phonenumber contains '1866***8532' - syntax error at position 30

cr7b6_num_phonenumber eq +1866***8532) - get's an error saying the '+' is not a vaid character

cr7b6_num_phonenumber endswith 1866***8532) - Syntax error at position 30

cr7b6_num_phonenumber has 1866***8532) Syntax error at position 30

contains(cr7b6_num_phonenumber,'1866***8532') - No function signature for the function with name 'contains' matches the specified arguments. The function signatures considered are: contains(Edm.String Nullable=true, Edm.String Nullable=true)

contains(cr7b6_num_phonenumber,1866***8532) - No function signature for the function with name 'contains' matches the specified arguments. The function signatures considered are: contains(Edm.String Nullable=true, Edm.String Nullable=true)

substring(cr7b6_num_phonenumber,1866***8532) - No function signature for the function with name 'substring' matches the specified arguments. The function signatures considered are: substring(Edm.String Nullable=true, Edm.Int32); substring(Edm.String Nullable=true, Edm.Int32 Nullable=true); substring(Edm.String Nullable=true, Edm.Int32, Edm.Int32); substring(Edm.String Nullable=true, Edm.Int32 Nullable=true, Edm.Int32); substring(Edm.String Nullable=true, Edm.Int32, Edm.Int32 Nullable=true); substring(Edm.String Nullable=true, Edm.Int32 Nullable=true, Edm.Int32 Nullable=true).

So I'm kind of at my whits end here. Can anybody help out? Thanks.

EDIT! The answer was this:

Turns out I had to -

Step 1: Run a flow that took D365 Voice Channel's incoming number and looked that up against the "Phone Numbers" table. The filter query for that is

msdyn_phonenumber eq '+1866***8532'

THEN once I had that GUID, I used that to search the bel_incnumber customer table. Because num_phonenumber was a lookup, it needed a 'eq [guid]' filter where the guid was the guid of a row in the looked-up table. So the second filter query looked like:

_cr7b6_num_phonenumber_value eq [variable-guid from step1]
Solution

  • are you sure your field cr7b6_num_phonenumber is lookup? Could you paste a screenshot of it.

    anyhow irrespective of your field and it's type, Take a look at XRMToolBox plugin called fetchxml-Builder

    It has a feature called power Atuomate Parameters which will give you exact column names you need for your power automate.

    I always use this and it saved me from the issues you are facing, I do not have to worry about what exact schema name to be used in power automate. Fetchxml builder does that for me.

    Here is an article explaining the same