Relationship Quandary

[Greg Hains]

Hi.

I am having a problem working out how I should configure a relationship and making it a multi-user affair. (Sounds like family issues doesnt it..)

I have spent many hours on this going in circles and am little the wiser. Offering money or services to whomever can help me may be wrong on this forum may be wrong and I withdraw this paragraph if so, but I am happy to engage somebody to get over this hurdle for me.

In short. I have two table I need to populate.

Once is a table full of items - lets say stock with item description and pricing and the like, and the second table is one with quotes for clients.

My current method (although not working), is to have these two tables as portals on a layout on a third table, that allows me to select an item from the stock portal, enter a quantity of that item, and have it create a new item in the quotes table - and so the relationship is based on quote number, so only the items added for that (new) quote are displayed on the quote portal. Sounds straight forward - I can do this bit.

My issue is that on the intermediary layout (that contains the two portals) seems to do my head in - muck it all up. It has to relay the information between the two (related fields) without storing the information in it, or having it so that multiple users can create quites with clashing on same records.

Does that make sense or did I make it more of a dog's breakfast.

Happy to provide more clarification and pay for services if need be.

Thanks,

Greg

[comment]

You do need a third table, but not as a interface element. You are describing a many-to-many relationship between Quotes and Items. This requires a join table of LineItems to be placed between the two - see a basic example here:

http://fmforums.com/forum/showpost.php?post/309136/

If you want to use a portal to select items for the quote, see:

http://fmforums.com/forum/showpost.php?post/355429/

[Greg Hains]

Hi Comment.

Thank you for your reply. Your examples are good and worked fairly well, but Im not versed enough in FmPro to know that the problem I came across is something Im doing.

Referring to the Portal example, if the relationship between the LineItems and Product tables is "=" then whilst I can display the full list of products in one portal, the LineItems table only shows the first item from the Product list - but as many of them as their are line items.

Just experimenting, if I change the relationship to "X" then the LineItem list is correct, but I get a cut down list of the Product list.

Sorry to be a pain, but what am I doing wrong?

Again, if the job is too big, Im happy to pay you and grant you access remotely.

Cheers,

Greg

[comment]

the LineItems table only shows the first item from the Product list - but as many of them as their are line items

It sounds like the fields in the portal to the LineItems table are from the wrong occurrence of Products.

[Greg Hains]

Hi Comment.

Thanks again. I have not correctly replicated the structure in my database.

I see that in the relationship diagram of the PortaltoPortal example, there are four tables drawn, but only three actual tables. The fourth table (AllProducts) is somehow related (maybe a bad choice of words) to the Products table but linked to the Invoices table.

What is this AllProducts occurrence? How is it created?

Cheers,

Greg

[comment]

There are only three tables, but the Products table has two occurrences on the relationships graph.

This is a VERY important aspect of Filemaker's relationships: it allows you to view the same table from different contexts. In this case, you can look at the invoice's sold products using the "Products" TO (which is related through LineItams and thus "fetches" only sold products), but you can also view the entire list of products using the "AllProducts" TO (which is related using the x relational operator, so that all products are included in the related set).

For more, see:

http://www.filemaker.com/11help/html/relational.11.11.html#1028192

and the topics in this section.

[Greg Hains]

Hi Comment,

Your explanation makes good sense - thanks for that.

But in a nutshell... Aaarrgggh (lol).

I have made the relationship as per the example, but still I still get the same results - either filtered/reduced product list or the same item listed multiple times on the Invoice portal (which I am using for quotes anyway - same, same).

Sorry to be a pain. :

I dont think I'm silly, but am tearing my hair out over an apparently simple issue.

Cheers,

Greg

Edited October 4, 2010 by Guest

[comment]

I still get the same results - either filtered/reduced product list or the same item listed multiple times on the Invoice portal

Which portal is that? If you mean the portal to LineItems, that has nothing to do with the AllProducts TO. In fact, it should work even if you delete the AllProducts TO altogether. Just make sure the product fields in the portal are NOT from the AllProducts TO.

[Greg Hains]

Excellent! Thank you Comment. All sorted. Problem eventually was that my product list portal was pointing to Products. As soon as I changed it to AllProducts everything fell into place.

I have nothing to offer you in thanks for this help as you clearly out-class me with FileMaker but if I can be of some assistance to you some time, dont hesitate to bug me. :

Cheers,

Greg