Bundled Product Circular Reference
I have a product table and want to make a product that is made up of other products (like a bundle product).
The issue is that when I try to calculate the total price of the bundle product by adding all of the child product prices I get a circular reference.
I get why this could be an issue but in my use case a product should never be a child of itself and so shouldn't create a circular reference issue.
Perhaps if my add custom function that explicity excluded the parent product? And if so I am having a nightmate trying to code it 
Any help or suggestions would be welcomed 
6 replies
-
Can you post a sample of your code?
-
You might have to consider making another table raw materials or something like that. Then you could make it a child of products. Then you can make a product that has many raw materials. A raw material can also be a product.
You can think of building a car. There are many raw parts that are needed to make an engine but you can also sell the spare parts.
Just some food for thought.
-
A similar problem has been treated here: https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/resolving-a-bill-of-materials-60099f17f3f37f54ab80cc7c
-
Hi kanepow
This requires a many to many relationship. An example of this would be students and classes. A student attends many classes and a class has many students. To allow for this you have a third table to join these two together. Your case is a type of Bill of Materials (BOM) called a Kit and you know its a many to any relationship because you can say a kit consists of many products and a product can be a put in many kits. The difference between this and the student/class example is that both sides use the same product table. In Ninox you are not limited to one reference field per join, you can have as many as you like, so...
You already have your Products table. Add another table, lets call it Kit. This is where it gets tricky working out which reference is which and although I've done this a few times now I ALWAYS get confused as some point in the development. Make your first join by adding a reference from Kit to Products (Many : 1). Use this join to add products to your kit and rename the reference fields to make the purpose of this very clear. Now add another reference field from Kit to Products (Many : 1) to hold a reference to the products in the kit. Again rename these to make their purpose very clear. Add a third field Quantity to hold the quantity of each product in the kit. Add a Kit Cost formula in Products to hold the sum of quantity * cost from the Kit table.
One last thing which isn't essential but good practice is, from the Kit table, set Compsition to Yes for the join to add you products to the kit.
I can mock this up and provide screenshots if you wish.
Regards John
-
Hi Alain
Just read you linked post and you are so right. I have assumed in this case there is just a single level. I started to answer a more complex post a few weeks ago and said I'd get back with a solution and quickly realised it had a recursive element to it. I have a multi level BOM in a FileMaker solution written for my own business without any note of the BOMs level or explosions and somehow it seems to know which cost to calculate first. I have never understood why or how it knows how to do that!
Regards John
-
Thank you all for your help, I think I have a solution. Your link @Alain was very helpful and got me thinking in the right direction.
So all I basically did was create a static field without a live formular in it. I then created a button and on click of the button it does the required calculation (summing parts and product costs for the "bundled" product). This creates a "snapshot" in time which will not get recurrcive and stuck in a loop.
I then widened the button to update all rows in the table and not just the record I was looking at.
I then got it to loop ~5 times to account for the number of product levels, I don't excpect products to get more than 2 or 3 levels deep so 4 or 5 loops should suffice (and I can press the button again to run if 5 more times). so all of the updates cascade up the chain and everything is updated accurately.
Obviously if a product is made a child of itself I can keep clicking the button for ever and the numbers will just get higher and higher.
Code for the button below if it is helpful to anyone:
for x from 1 to 5 do //This is the numebr of loops to account for product levels
for i in select Products do //This selects the Products table where a produce can contain other products as a sub-table
i.('Static Total' := 'Parts Total' + 'Child-Product Total'); //This is the calculation, it adds together the cost of parts & child products
end
end
Content aside
- 3 yrs agoLast active
- 6Replies
- 567Views