Self join relationships

[Northshoredave]

Hi all,

 

I am trying to make a layout in my database that will help in preparing estimates. I want filemaker to return a price when I set two values. In order to do this I made a table (Price Schedule) with fields for Vendor, Item and Price. Then I created a self join relationship with this table using Vendor as the match field. In the layout there are two drop down menus, one for Vendor and one for Item.  Now when I select a Vendor, the Item menu (correctly) displays only the Items that that Vendor offers. Since this worked I made a second self-join this time using Vendor and Item as match fields. This does not work, setting the Item menu has no effect at all. After the Vendor is selected, filemaker does put in a price but this value doesn't change when a different Item is selected.

 

I think I really should be able to do this using the relationships panel, but I can't figure it out. Can anyone help me? 

 

Thanks in advance,

Dave

[Wim Decorte]

how have you defined the price field?

[Northshoredave]

It's a number field

[LaRetta]

Hi Dave,

 

Can you explain the purpose of the self-join and what you are trying to accomplish?  In reality, you probably would want a true relational structure (instead of self-join) if one vendor can have many prices for a Product.  Here is a file showing a standard relationship which allows adding multiple prices to a vendor (and specifying the Product) and also selects the best vendor price for adding to invoice LineItems. 

 

Take a look at the attached fp7 and see if it helps and if I've missed the mark entirely just let us know.  :^)

Products.zip

[Northshoredave]

Hi LaRetta,

Thank you for responding. I fudged a bit in my initial post, thinking it would make the problem clearer. It would be more accurate to say that many Vendors have many price codes, but each price code has one price. In my workflow, I have already selected the group of plants that I want to use for a project. I now have to assign a price to each one. Because there are so many different plants they are not priced individually but in groups, hence the codes. Actually this is quite handy because by keeping the prices and codes in a separate table, I can update them easily rather than having to update the (many!) individual items. Since the entire list of Vendors and Codes would be cumbersomely long, I thought to first sort by Vendor and then select a Code from the now much shorter list. Having selected a vendor and code, I want FM to fill in the price.

I think your example is really quite close to the mark, only it suffers (on my machine, anyways) from the same problem that I already have. If I select Gangling Wrench in Invoices, Western Utilities and $5.99 are automatically filled in. Great! If I change my mind and select Kelp Industries, the price does not change. This is strange as your solution is structured entirely differently.

I hope this makes things clearer.
Thank you,
Dave
 

[LaRetta]

Right, I see. You can attach VendorPrices directly to LineItems (on ProductID and VEndorD) and then reference THAT table occurrence in the prices auto-enter calc.  It refreshes properly.  You could use script trigger also but scripts should only be used when the natural relationship can't flow by itself (and this should).

 

I am not sure of the theory on why LineItems, when triggered via its own records' ProductID and VendorID will not update when pulling from grandchild but direct connection works. All I can think of is that It might be the timing on the LineItems VendorID which is also auto-entered.  Maybe others can explain the logic behind its failure?  Regardless it's another thing I've moved to my 'must figure out' list, LOL.

 

Note too that I have the LineItems Price set to auto-enter (Replace).  Be sure and include a test in the calc or preferably use Privileges that it cannot be changed after posting month end.  Sooooo many options in FM, it can be difficult to mention all the important aspects in one post on the fly. 

Edited December 20, 2012 by LaRetta

[Northshoredave]

Thank you, LaRetta!

 

That was the problem, it works great now.

Theses are the things they don't tell you in books!

 

Dave