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!
