inverting a relationship

[daniel z]

Hi All !

I have a filemaker pro 6 database with fm server 5.5

The relationship is as follows:

- I have a contacts database and a products database (only about 30 products currently).

- A contact can purchase any of the products.

- When he/she does this, a record is added to "contact_product_summary" database with the product id and contact id.

- I can thus show all products that a contact has purchased

Simple so far.

- Now what if I want to INVERT the list and show, for a particular contact, all the products that they DONT have ? This came up in (Topic#136734) but I couldnt really understand what they were saying and if it only applied to FM7 or not.

I really need to do this but I have no idea how...

Also another related problem is that with the current system, a particular product never stays on the same place on screen -- depending on which products a contact has (and how many), a particular product might appear high up the screen or low down. Despite the fact that the products are listed alphabetically, it is still harder for users to to locate the product they are looking for than if the product was always in the same place on screen. Now I could simply add blank entries for all the products for every single contact, but I have 40,000 contacts and it

seems like a real inefficient way to do things. Can anyone think of a better way to do it ?

Thank you !

Daniel

[Ender]

Hi Daniel,

I don't think there's a good way to do this in FM5/6. Relationships require an exact match to an existing record for a match to occur. This is no longer a limitation in FM7/8, where relationships can be made on inequalities.

Unless you can think of a better process that doesn't require a "not purchased" list, I'm afraid you're stuck with creating a join record for each Contact-Product pair.

[daniel z]

Thank you, Ender.

[comment]

Well, it could be done by a script (take a list of PurchasedIDs, and run it against a list of AllProductIDs).

However, the second request is easier, and it might solve the first one as well. Note that you must keep the gCustomerID in Products synched with the currently viewed customer.

Customer_Sales_v.4.zip

[daniel z]

Thank you, Comment. That is a brilliant solution! I had a vague idea that something of the sort would work but I had no idea that globals could be used like this. What happens if 50 different users are online at the same time ? Will there not be a conflict between globals when this happens ? I've read somewhere that global values are stored locally on each machine - is this why it would work ?

(and yes it would solve the first problem as well)

I dont understand your solution to the first problem by using a script -- can you elaborate on this please?

Thank you again ! I was at my wits end and about to create 800,000 blank records as the only solution I could think of to this problem -)

[daniel z]

Actually I think Echo in Post#197025 answers my question:

"...In an multi-user scenario each user can have their own values for a global field without impacting other users. There are many instances where this is useful. "

[comment]

A scripted solution would use 2 lists, produced using the ValueListItems() function. One is list of PurchasedIDs, the other a list of all ProductIDs.

First, you set a text field in Customers to the list of all ProductIDs. Then, using a global field as a placeholder, you take the first value from PurchasedIDs list, and remove it from the text field, using Substitute(). Loop the process until you have reached the last value of the PurchasedIDs list.

The values remaining in the text field are the IDs of unpurchased products, so a relationship to Products based on this field will show all products unpurchased by this customer.

[daniel z]

Thanks again comment.

I've just implemented your non-scripted solution on my database and have some very happy users !