Dataverse List Items Expand Query - only recognizes $expand/$=select for the first column in a "level". Is this a known limitation?

[MelissaReed]

I'm trying to use Expand Query to select records from a table and also pull in values from related tables

The structure is

SRP  -->> SubSRP  -->  Resource

                               --> Status (lookup to get a switch value)

        --> Status (lookup to get a switch value)

The expand query I'm using is:

crd60_SubSRP(

   $select=crd60_name

                  ,_crd60_srp_value;

                             $expand=crd60_SRP($select=crd60_name )

                 ,_crd60_subsrpstatus_value;

                             $expand=crd60_SubSRPStatus($select= crd60_iscollectingtime)

    )

This returns the SRP info I'm asking for but not the subsrpstatus info I also requested.   

If I reverse the clauses and put subsrpstatus before srp , it returns subsrpstatus but not srp info 

Can anyone tell me if this is a limitation of OData -- the branch can go deep but only one $expand per level?  Or is there some syntax glitch I'm missing?

Here's a  screencap of what I'm trying to do -- both clauses work but only the first one called gets data returned.

[gabibalaban · Accepted solution]

@MelissaReed ,

Actually, for complex query like this, you should try Fetch XML query option.

You can use XRM ToolBox -> Fetch XML Builder for an easy build of your XML query and also to try it before you put in your flow. 

Give it a try, it changed my "query" life ! 

[MelissaReed · Accepted solution]

Congratulations, @gabibalaban  You made "Fetch" happen!   

Why did I resist exploring this for so long?!    I do have XRM Toolbox so I decided to invest 15 minutes to see if I could  get anywhere with it (I spent far too long futzing with Expand Query).

In < 15 minutes I accomplished what I could not get to work for the past 2 days or so.  And the result set is cleaner!

For newbies (like me) this getting started tips once you're in XRM Toolsbox's FetchXML tool (and connected to your data source)

1) Choose your Table:

        Select the first Subnode under "Fetch 50"  (on the upper LH panel)

        Below in Quick Actions, click the first drop down to choose your "root" table from the dropdown list

        (you'll see it change in the panel above -- verify it is the table you meant to choose!)

2) Choose your columns from that table

While still in Quick Actions, Click "Select Attributes" and choose the fields you want <<as fields>> from the choices that appear on the right.    You'll see them added as subnodes in the LH panel.    Don't worry about lookup columns yet.

4)  Set up your Lookups for that table   (linking to lookup tables like Customer, Status ) 

a) Still in the Quick Actions panel (lower left), Click "link-entry" link

b) Choose your Relationship from the first dropdown -- don't be scared!  just look for your keyword in the relationship name --  Choosing that will fill in Entity Name, From, and To fields for you!

c) Choose a Link Type: 

               "Inner" if you only want to include records where a match is found in the lookup

               "Outer" if you want the lookup values to show as null if no match is found (main record is kept)

d) Enter an Alias  -- by what "nickname" do you want to call this relationship?  Simple  tablename is fine but it has to be unique in your entire query.  

5)  Define your filters for that table 

       a) Still in Quick Actions panel (lower left), click the Filter link

b) This opens a panel where you can name the table's column,   the operator, and the value to compare to.  (hint: booleans are 0 and 1)

Follow this pattern for each "level" to do your lookups, filter your results

6) Test your results:   Click the Execute(F5) button on the tool's top menu 

7) Generate the FetchXML code:   Click on View button on the tool's top menu   (or just click Ctl-E)

😎Paste that into your P/Au Action's FetchXML parameter   (remove the "Top 50 parameter, or modify if you wnat to limit # responses)

Here's what the finished product looks like in FetchXML of what I was trying to do:

And after I pasted it into my Dataverse/GetListItems this was what was returned in "Values()" -- see how much cleaner this is to work with ?!?!?!

Swallow your fear -- if your P/Au code is clunky (and slow) because you're breaking everything in to multiple nested queries, give this a shot.   

Thanks again @gabibalaban    You've greatly improved my quality of life far beyond this one Action step!