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 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:
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.
Keep up to date with current events and community announcements in the Power Apps community.
A great place where you can stay up to date with community calls and interact with the speakers.
Check out the latest Community Blog from the community!