Convert Query into Fetch-XMLQury

Question

I want to change select query into FetchXML Query. My query is

SELECT * FROM new_periodtotal    
JOIN product ON product.pruductid = new_periodtotal.product
JOIN new_program ON new_program.product = new_periodtotal.product 

Problem is there is no direct relationship with new_periodtotal and new_periodtotal both have relationship with Product. how I convert it in fetchXML query.

Answer 1

I don't have a structure like that at hand to test this out, but I'll try to extrapolate a solution based on the OP query. Some additional tuning / trial&error might be required.

If product.productid = new_periodtotal.product and new_program.product = new_periodtotal.product then product.productid = new_program.product.

You in fact have a realtionship, though it's "indirect": both entities would have non-empty "product" fields.

To obtain the FetchXML I would build an Advanced Find like this:

Search entity: product

• (Related) new_periodtotal
  • (product field) -> contains data
  • (related) new_program
    • (product field) -> contains data

The third row is meant to look in the related of the related, hence the increased indentation.

It would read like this: "get all product records where the related new_periodtotal records have a non-empty product field, and where the related new_program records of the related new_periodtotal records have non-empty product field".

After that, you can Download FetchXML (up in the ribbon) to get the query.