Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have two tables "sales" and "purchases" linked between the product.
Also, the two tables have a "date" field. I want to filter two tables at same time in function of same "date". I can't link tables using date field, because I get a circular reference error.
What can I do? Thanks!
As my understanding: SalesOrder and SalesOrderLine (order detail) are linked by SalesOrrderID. (same principle for Purchases).
Base on your business, you can have 2 ways:
1. Combine SalesOrder and SalesOrderLine into 1 table (by Join or ApplyMap). After that you can follow the model above.
2. Bring Date field from SalesOrder to SalesOrderLine by using ApplyMap. Then delete the Date field in the SalesOrder. And your model will be like this:
How does the data model look?
Can you share screen shot?
Yes, but it is in spanish.
For simplicity:
For example I want to select "november 2017" and get all sales and purchases for this month.
Hi Jose pls go thru below 2 links. Iwas facing similar challenges. hope it helps. Thanks
Hi Jose,
May be you should design your data model like this:
CompositeKey is created from ProductCode & Date.
You can view the demo script in attached qvf. !
you can link those tables using Link Table and Composite Key.
It's a good idea. How can I implement it with a PurchaseOrderLine table and SaleOrderLine table?
The "Sales" and "Purchases" table have the Date, and the "...OrderLine" tables have the ProductID.
As my understanding: SalesOrder and SalesOrderLine (order detail) are linked by SalesOrrderID. (same principle for Purchases).
Base on your business, you can have 2 ways:
1. Combine SalesOrder and SalesOrderLine into 1 table (by Join or ApplyMap). After that you can follow the model above.
2. Bring Date field from SalesOrder to SalesOrderLine by using ApplyMap. Then delete the Date field in the SalesOrder. And your model will be like this:
HI, I have similar problem, however with 3 tables
Sales, Order and Stock level.
Will be glad if i can get some help
Help with joining multiple tables while comparing dates (Date In)