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

[Connor55]

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.  

Inventory

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

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

PullSheet

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

Relationships

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.  

@poweractivate

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

[poweractivate]

@Connor55 

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 https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate 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.

[Connor55]

@poweractivate 

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?

[poweractivate]

@Connor55 

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.