Jump to content

inverting a relationship

daniel z

This topic is 5733 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 !


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 }:(-)

Link to comment
Share on other sites

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. "

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5733 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.