Hi

I'm quite new to Filemaker and databases in general and I'm struggling with solving a concept which I am assuming is natural territory for Filemaker and databases in general. I just can't seem to figure out what to to do in terms of relationships. I'm convinced the solution should be elegant and simple but I am really stumped.

In addition to other features I want to create a database that will track an inventory of raw photographic chemical consumables some of which will be combined to create other consumables which again may be used to create others before they are actually used in a process. Basically, I need to track things like chemical solutions that I have mixed myself back to the source (where I purchased the constituent ingredients).

To put it another way (and ignoring the photographic aspect), if I were to buy table salt and distilled water they would be regarded as two separate consumable items and I would have a record of how much, when and where they were purchased. If I then mixed some of the salt and distilled water together I would be creating a new consumable (salt solution). That would obviously be a child of the salt and the water but would also be a brand new consumable and be perfectly entitled to reside in the same table as the salt and the water (where I want it to reside) and therefore be available to act as a parent itself - in the same way a human child would expect to be listed in the same table holding the details of the mother and father that created it. Is this possible? If so, how?

Really sorry if this is blindingly obvious but I really have been blinded by it.

Really hope someone can help me with this.

Many thanks

James

This thread:

http://fmforums.com/forum/topic/58493-immediate-and-extended-families/page__p__276742#entry276742

has an old file attachment - "tree" - (the file has a number of bad practices) - that will allow you to create relationships within the same table and could be adapted to fit your needs.

Really sorry if this is blindingly obvious

No, it's not obvious at all - in fact it's rather complicated.

First, you have one thing right: all your chemicals need to be in the same table, whether they are raw materials or compounds. However, a salt solution is NOT "a child of the salt and the water" - at least not in the sense we use the terms parent/child in the context of a database. The relationship between a compound and its ingredients is many-to-many and requires a join table of Quantities. The complication here is that both parents of the join table are occurrences of the same table.

This thread:

http://fmforums.com/...742#entry276742

has an old file attachment - "tree" - (the file has a number of bad practices) - that will allow you to create relationships within the same table and could be adapted to fit your needs.

Personally, I found it more confusing than helping.