Unique records via join or import?

[LindaG]

Vs. 6, XP - I need to find each unique product a customer has purchased and list the last date they purchased it. I need this unique product list in a portal displaying in my Customer file.

I have a 300,000-record lineitem file. I originally thought self join CustomerID & ProductID as both keys then calculation (number) with: Case(Max(selfjoin::Date) = Date, 1)). I've used something similar for other things. Then find on the 1, then loop Set Field [Last, 1] then the 1 would be the right key for Customers constant 1 (because the Max calc couldn't be used for the right).

I'm considering another idea and please see if it's viable ... Do the above only once then delete the Max calc and relationship. Write those 'last' customer products to another file to prime it then, whenever I import new lineitems from another existing FM6 file, (it's a long story) also pull those same records into this new file - matching on a calculation of ProductID and CustomerID - and overwriting the date (if an import record exists) - and adding new records (unique customer/product) if none exists. This *appears* much simpler. When I think about searching through 300,000 records (and setting a field) daily which also contains a Max calculation, I break into a sweat.

Has anyone else done something similar and do you see any problems in my thinking?

Linda

[ESpringer]

I don't see why you need the separate file (or table on FM7). By all means, after the initial calc, yank the field definition back to numeric, so that the status remains flagged for the most recent purchase *of* that item *by* that customer, without sluggishness. (Essentially you've already done this.)

But have a relationship still intact between all purchases *of* an item *by* a customer. (This could come in handy for other things too!)

Then, when you perform an import batch, the most recent of the new related purchases (of item by customer) gets a 1 flag indicating "most recent purchase of __ by __" and a script sees whether there's a related record (might as well match also on the flag field) from before the import date, whose old 1 is then deleted/set to 0.

Why did you want to set up another table to hold this? Searching through 300,000 records when 20,000 have a 1 flag as the match key shouldn't be any worse than searching through 20,000 records... Once FileMaker has an index of the 1s, it essentially narrows attention to those as if you're working with a smaller table, I believe.

Anyone have another intuition about this? It seems to me that you almost never want to have two files, or two tables, where the records are for exactly the same *kind* of thing...

[LindaG]

"you almost never want to have two files, or two tables, where the records are for exactly the same *kind* of thing... "

Oh yes! I agree. This isn't networked. I will be exporting parts of four files every day anyway - only the last two years' worth of lineitems (stand-alone systems for lookup only), so one more small file didn't seem to matter and I couldn't figure how to isolate the lineitems to eliminate rehashing the entire file each time. Your thoughts make sense, thank you!

Two questions? so I join CustID & " " & ProdID & " " & Flag

1) After I import (and set my Flag 1 through the set), I then perform a find on the 1, then do I then loop back through that found set with something like:

Go to Record/Request/Page [ First ]

Loop

If [ IsValid(SelfJoin::CustProdKey ]

Set Field [selfJoin::Flag, ""]

End If

Go To Record/Request [Next, exit if last]

End Loop

2) Sometimes we import more than one day. That shouldn't make a difference should it? Because all flags will be found and it will run through the records deleting the flag until there is only one - the newest (and the join becomes invalid).

Is this the right track? Thank you again for helping me.

Linda

[Fenton]

I would agree with ESpringer, there's no need for another file. There may be a need for a script. It's difficult to mark ONLY the last of something, because it means that you have to remove the mark from earlier entries, those with the same CustomerID and ProductID.

If you had a "CustomerID_if_LastProduct" field. This field could be targeted by a simple relationship from Customers, or easily found for a report.

A script would, after choosing the Customer and Product:

Go To Related Record [show, "self_CustomerProduct"]

Loop

[LindaG]

Hi Fenton, there is no order entry (invoices/lineitems are pulled from PeachTree currently). And I think I forgot to mention this is in vs. 6. I got out of bed because I realise my script (above) wouldn't work anyway because it would still clear the last flag.

I keep thinking I'd still have to use a Count() or some other means to isolate them which would affect the entire regular lineitems file when all records are displayed; but I can no longer see straight or think straight. I too will check back in the morning and reread all of this, if possible. I DO know that working with this size of file, particulary using Max() and even setting fields through it, is very time-consuming - and that's why I wanted to isolate the correct records somehow. Thank you!

L

[Fenton]

I think we're more or less on the same page. A couple things though.

Go to Record/Request/Page [ First ]

Loop