Searching in many to many relations

[temptraq]

Hi!

Running 5.5

To avoid many-many relation i

[RalphL]

As I see this Customer has a one to many relationship with Order. Order has a one to many relationship with Order Line Items. And Article has a one to many relationship with Order Line Items.

The Customer ID is the primary key in Customer and the foreign key in Order. Order ID is primary key in Order and a foreign key in Order Line Items. Article ID is primary key in Article and a foreign key in Order Line Items. The primary key in Order Line Items is a concatenation of OrderID and Article ID.

You can bring Customer ID into Order Line Items either by a calculation or a lookup. A portal in Article to Order Line Items will show all customers who have order that article.

[temptraq]

Yes Ralph, but how do i find customers that have ordered 102 but NOT ordered 98?

As you can see customer 303 has ordered article 102 (row 4) and so has customer 256 (row 7). But only customer 256 has ordered article 102 but NOT 98.

[RalphL]

I think I would use the Troi Text Plug-in (http://www.troi.com) to make a list of all customers who purchase 102 and a list of those who puchased 98. XOR the 2 lists will give a list of those that did not purchase both. AND this list with the list of 102 purchasers would give a list of those who purchsed 102 and did not purchase 98.

[-Queue-]

You could also create a value list of articles based on a self-relationship of ID. Then create an unstored calculation number field of PatternCount( "

[temptraq]

Thanks RalphL (sorry for missing L in last post).

A plug-in is not affordable since we have over 500 licenses of filemaker.

Queue: Looks like what i

[Fenton]

A scripted method, doing more or less what the other's methods are doing, but which may or may not be faster (it depends on how many customers total, how many bought the specified article; if "a lot of customers" and "only a few" then it would be fast). Try Queue's first, see how the speed is.

Use a relationship (with a global) or Find to isolate the 102 orders, in the order line items.

Use the Copy All Records technique to copy only the CustomerIDs.

Paste into a global (either here or in Customers)

Exit Record

Go To Related Record [show,"global IDs to CustomerID"]

(going back to Customer table)

That's the Customers who bought 102.

Fields in Customers:

_gOrderNot (global; enter "98")

_cgCustID_OrderNot (calculation) =

CustomerID & " " & _gOrderNot

Relationship to a calculation field in Order lines, let's call it "Ordlines__cgCustID_OrderNot"

Field: CustomerID & " " & Article#

Freeze Window

View As Form

Go To Record/Request ["Last"]

Loop

[RalphL]

I just checked the pricing of the plug in. It is $9 each for 100 or more. If you can afford 500 seats of Filemaker that would be a minor exspense.

[temptraq]

Hi RalphL!

The cost for 500 FM are motivated by the income it generate. To spend $4500 just for a quickfix of the system is not.

Hopefully some day i

[temptraq]

Queue, works like a ,SLOW, charm!

Have 150.000 posts in order and i know i have 4050 orders with 102 (acctually 139 and that caused me some headace because i did

[temptraq]

Reconcidering...

Calculated that this is gona take 3sec*150.000 posts = 125 hours!!

I

[-Queue-]

Yes, 150000 records would take a while. It would probably speed up the process to do the calculation from the Customers file since there would only be a single record to search. You would create a relationship from CustomerID in the Customers file to CustomerID in the Articles file. Then create a value list of articles based on this relationship. Then use the above calculation. I'm curious to see what the speed difference would be, even if you end up using the script instead.

Oh, it also might help to change the PatternCount functions to Position( "

[temptraq]

Hi!

The work is done in the live db, the "ugly" way. Creadted a field, "found", searched for 98 (in portal) and Replaced "found" with "1" and then searched for 139 (in portal) with 2:nd request Omitting where "found"=1. It works but are not "smart" enough. Want a dynamic solution so that all users can do it and alter articels...

Now i

[Fenton]

Yes, time is the enemy whenever you do a Find on related fields in a large file (though it's much faster in 7).

The scripted method I outlined should not take all that long. But the Loop would not be so fast, with 4050 (but some of these have the same customer?).

Your "external file" method would be even faster. Good work -)

It would be a permanent file, import not export.

OrderLines, Control script:

Find (or use global relationship to isolate) "yes" records

Perform Script [ External, (below) ]

In External file:

Show All Records

Delete All Records

Import the "yes" records

(the CustomerID is the only field you really need; you may want the other field at first, just to check that I'm not crazy :-)

Back in OrderLines Control script:

Find "no" records in OrderLines

Perform Script [ External, (below) ]

External:

Import the "no" records.

(continue)

Copy All Records, CustomerID ONLY*

(Found set is "no" records)

Paste into a global

Exit Record

Go To Related Record [show, "self_CustomerID global to CustomerID in this file"]

(These are the people who bought "no" item, whether or not they also bought "yes" item. It will have both types of records.)

Show Omitted

Copy All Records, CustomerID again

Exit Record

Go To Related Record [show, "CustomerID global to CustomerID in Customer file"]

*There is a 64,000 character limit on fields in versions pre-7. So, depending on the length of your CustomerID and the number of found records, you can exceed the limit.

(P.S. For some darn reason the Scripts Menu does NOT return correctly at the end of the script on my system, Mac OS 10.3.4; no scripts show. I tried just about every kind of window refresh thing I could think. No dice. But as soon as I do anything in the file, or click outside FileMaker, it fixes itself. Never seen this.)

CAROmit.zip

[Fenton]

Actually, here's a better file. It uses relationships to go to the Items. The earlier one used Finds (sloppy). I wasn't able to switch the attachments; there's a glitch right now.

CAROmit.zip

[-Queue-]

For users to be able to change articles, you would simply need two global fields, replacing the hard-coded numbers with the field names.

The external file sounds good though. Use whatever works best for your situation.