Creating Sync Views across Multiple Sync Sources in EDA - Administrator

I am attempting to create a custom View (or table) from the EDA - Sync Sources page, and running into challenges when joining tables from different sync’d sources.

I am finding that if the joining table has a single relationship (ex product_id = product_id), the query will execute with expected results. If the joining table has multiple relational attributes, the expectation will fail. (ex. customer_id = customer_id AND product_id = product_id). I have tested this on multiple sources and fields, and have also casted and converted data types to exclude that from the underlying problem.

Has anyone run into similar challenges, or have any suggestions? Any insight is greatly appreciated!

Hi divic,

What you mention should be a very standard view and indeed possible. I should not think - neither have I experienced - that there are special concerns or limitations when the Items are placed in different Sync Sources.
I have made similar views many times, joining across Items and other views and Sync Sources.

Most often, the error result gives a good hint on what the error may be (not in your screendump though…).
Sometimes I use the Custom Action SQL Query to build and test statements, as this have got other timeouts and sometimes gives other error details.
Just have this on the first line:
USE [SYNC];
to specify the Sync DB is your target.

Are you dragging and dropping your Sync Items into your query?
I’m not that SQL savvy to comment on your SQL and if that is 100% valid :slight_smile:

Hi hakio, appreciate the reply, and agreed with all you mentioned. It turns out the issue causing the query to error/timeout was stemming from the original set up of our Phocas Standard Tables, which were created as views from split files (and obviously creating a view from a view is not an efficient/recommended practice). Phocas has since rebuilt our tables as standard tables which alleviated this issue.