Forum Discussion
Relation between two columns is not woking
Is it true that each opportunity is only ever tied to one and only one order but an order can have multiple opportunities? Or is it that it's a M2M relationship between OrderId and OpportunityId?
If it's M2M, you'll need a bridge table or a weak M2M relationship with some kind of wisely chosen filtering. I would not recommend cross-filtering because it's dangerous.
Best
Darek
Hi Anonymous ,
Thank you for responding to the question.
One opportunity can be tied to:
- Only one sales order.
- Multiple sales orders.
A sale order can be tied only to one opportunity, but multiple sales orders can be tied to that same opportunity. Thus, in my opinion, the appropiate relations between both variables should be one to many.
I have looked up for a bridge table, but I have not found any.
If you have any other suggestions, please let me know.Best regards,
Sara.
- To know what's going on we have to see the measure(s).
Please confirm that there is a one-to-many relationship defined between crm_lead and sale_orders. To which table is the table of dates related? Because... you do have a proper Date table in the model, right?
Best
DarekHi Anonymous,
The relationhip between crm_lead and sale_order is one to many. For now, I am using the dates provided in the same table, that is, create_date, to filter sales orders. Later on, when I make sure that everything works, I will create a Date table.
If you want a sample of the data, please go to:
sale_order: https://drive.google.com/open?id=1_j_OTx3Bmq-0HVIecpwDFWScDBWsKZVK
cm_lead: https://drive.google.com/open?id=1ZROMqLmjw5mQN8Auf_VXPAug30__8XYr
Thank you,
Sara.
