Showing results for 
Search instead for 
Did you mean: 
Resolver I
Resolver I

Date Dependent n:n Relationships - This is tricky, how would you handle this?

Here's my scenario and what I have setup so far:


I'm working with an event production company - to simplify, I'll stick to the three main entities.  



A list of products with product ID's & barcodes.

There are two types - serialized and non-serialzed.

Serialized items are Product ID's that have many barcodes - the product is the same, but each item gets it's own barcode.

Non-Serialized items have only 1 barcode to 1 Product ID.  They have a quantity # of more than 1.



Show name, Show ID, start date, end date, budget, location, etc.



Each show is attached to a Pullsheet entity.  This is a list of inventory items needed for that particular show.  



Inventory n:n PullSheet n:1 Show


How would you handle tracking stock??

There are two things I can think of that need to happen to prevent overbooking inventory items:


1. Inventory should only be Related (I'm referencing the Related/UnRelated Functions) during the actual dates of the Show/PullSheet - or maybe 1 day before and after.  Otherwise, a PullSheet created for a Show 3 months in advance would affect the amount of Inventory available before the Inventory is actually needed.  


2. I have an InStock and Owned Stock quantity in the Inventory entity - when an Inventory item is Related to a PullSheet, the InStock quantity needs to subtract from the owned stock.  This is, again date dependent, but I think this function will work at the same time as the above function.  Just not sure how to write it.  



@Shanescows love your vids, would really appreciate your input on this




Regarding Relate/Unrelate functions:


We believe Relate is almost equivalent to setting a Lookup field, and Unrelate almost equvalent to setting it blank.

The difference is that a Lookup field need not be present at all as it operates on the relationship.


We also believe that N:N relationships do not technically "exist" on the system Common Data Service. There are really 1:N and N:1 only from our investigation into this.

Even this statement from just makes this seem to be even more likely:


"For many-to-many relationships, the system that links the records maintains a hidden join table. You can't access this join table directly; it can be read only through a one-to-many projection and set through the Relate and Unrelate functions. Neither related entity has a foreign key."


We are not primarily relying on the statement above, though, but rather, an investigation over time into how the system really works.


Therefore, for more flexibility, you may want to set up the one to many and many to one yourself, as the any to many actually is a one to many and many to one in disguise, if you set them up 1:N and N:1 manually you have more control.


Regarding the rest of your query about tracking stock:


  1. We are unsure Relate and Unrelate are much different from setting a Lookup field conceptually.
  2. Therefore, we might suggest that you try modeling 1:1 as an attribute and use Business Rules as described here in our reply o your previous post What type of relationships do i need? 
  3. One caveat not discussed above in much detail (or actually even at all) in our reply, was the quantity piece. We did not mention the possibility that you also need a Quantity field somewhere in this scenario, even if it could have been surmised as early as when you mentioned Serialized and Non Serialized items. That would be especially for Non Serialized. Even Serialized - maybe each Inventory still need a numeric Quantity attached to it for each Inventory - however, that Quantity would probably be either under a line item from PullSheet or somewhere like that - maybe this is part of your question.
  4. To subtract from stock you decide first where you put this Quantity. Next, you can subtract a Quantity by saying Quantity -1 in PowerApps.
  5. As for removing the Inventory completely from Pull Sheet Line Item if it is Zero, you could check if it is zero. and this is where your Unrelate can help - because you may want to use Unrelate to perform the operation on the relationship level, rather than  worry about setting an explicit lookup field (just note that the concept of Relate and Unrelate is still similar to directly setting and unsetting a Lookup field - a Lookup Field is in fact added every time you make a Relationship in CDS because the Lookup Field stores the info about the reference).


The above could be an approach to start with.

A complete solution, however, would be very involved for this.

Perhaps the above could be a high level starting point.


For Date dependent stock subtraction, etc. you may need to consider Power Automate (Flow) so that action is taken upon the stock with or without the PowerApp Canvas App itself having to be open, depending on the scenario.


What do you mean about more control?  Doesn't the n:n relationship do the same thing as setting up 1:n and n:1 separately?



You're right, the N:N is better sometimes for a specific reason. For example, in your case N:N may be better because if you want to do something like emulate a "multi selectable lookup" - there are multi selectable Option Sets but those have a fixed value, and unfortunately there is no such thing as a "multi-selectable lookup". So the typical solution to do something like that, say, from a "Model Driven App" is instead to use a N:N and then use a Subgrid inside the Model Driven App form to be able to accomplish this.


In that case, the N:N may be better for you for cases like that, so for that part, especially wherever you are using N:N for this specific reason, you may want to keep doing it that way.

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,882)