I'm working on FM7 in XP. I have three databases: Orders, LineItems and
LineItemsDaily. Each record in Orders contains an OrderID. Each record
in LineItems contains an OrderID, PartID, DateOut and DateIn. There
may be many LineItem records for each Order record. Each record in
LineItemsDaily contains OrderID, PartID, and Date. For each LineItems
record there are several records in LineItemsDaily, one for each Date
between DateOut and DateIn inclusive.

I want to script a Find that will find the earliest Date for each
LineItemDaily combination of PartID and OrderID for a collection of
LineItems.

So far, I've created a calc field in LineItems that returns "OrderID
PartID DateOut" and a calc field in LineItemsDaily that returns
"OrderID PartID Date". So I can find the LineItems I want, say 17
records, each with a unique calc field. How do I then use that to find
the 17 records in LineItemsDaily with the same result in its calc
field? I've created a relationship that connects LineItems to
LineItemsDaily based on "OrderID PartID Date" but don't know where to
go from there.

Thanks,
-G

In article <1126378107.124656.244580 (AT) g47g2000cwa (DOT) googlegroups.com>,
grip (AT) cybermesa (DOT) com says...
Hi,

Generally its difficult to go from a found set in on database to a
foundset in a second table that is the aggregage of found sets from a
relationship in the first record. It can be done, but its not exactly
clean, and I'd suggest 2 alternative approaches instead:

The first approach is simple to build, understand, maintain, and
implement... assuming my assumptions are correct.

My assumptions:
1) The DailyLineItems are created via a script that loops from dateout
to datein.

2) The DateOut and DateIn are not subject to change.

If this is correct, then simply add a field called 'firstinset' and
modify your script that generates them to set the 'firstinset' to "1"
for the first record, leaving the rest blank.

Then your find becomes as simple as searching for the 1's in the
firstinset field.

---

Alternatively you can continue in the line you've done, defining a self
join sorted by relationship on the date in lineitemdaily, and then
define a field 'firstinset' that tests equivalence with the first
related record by this relationship, evaluating to a 1 if its
equivalent, blank otherwise.

(Note that you don't need a calculation in lineitems for this, as it can
be accomplished entirely in lineitemsdaily.)

Then as before you simply find 1's in the firstinset field.

The essential difference between the two approaches is that the former
sets the firstinset field when the records are generated, while the
latter computes the firstinset field on the fly.

The advantage of the latter is that if the set changes it updates
firstinset automatically... the disadvantage is that its much slower.
Instead of searching an indexed field your searching an unstored
calculation that draws its information through a sorted relationship.

Based on what you've said so far, I'd be leaning towards the first
anwser. Its simple and will work at full speed no matter how big your
database gets. And if the assumptions about how the dailylineitems are
created are true it should work very well.

The 2nd approach, is a little more robust, and is from a database design
point of view is more aesthetically pleasing - it keeps the database
more normalized, etc... but performance will drop like a rock as the
database gets big.

-Good luck,
Dave

Just a note for any who may not already be aware: FM8 provides this
feature natively, with no fancy work.

42 wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

There are 2 other ways I can think of doing this;

1) you already have calcs in both the LINEITEMS and the LINEITEMSDAILY
table that are a combination of "Order ID", "Part ID", and "Date". You
also said you've found the correct records in the LINEITEM table.
Instead of doing a find, copy the calc field for those LINEITEMS
records, paste into a global, then go to related records from global to
calc in LINEITEMSDAILY (the way you had to do it in FM6).


2) create a relationship from LINEITEMS to LINEITEMSDAILY using all 3
fields so that;
LINEITEMS::Order ID = LINEITEMSDAILY::Order ID
LINEITEMS::Part ID = LINEITEMSDAILY::Part ID
LINEITEMS:ate Out = LINEITEMSDAILY:ate

Keep in mind that in FM7 relationships are bi-directional. Instead of
doing your existing find in the LINEITEM table, do the find in a
LINEITEMDAILY table occurrence which links to LINEITEM using the above
mentioned relationship.

Thank you all.

I will try FPs 2nd solution first then 42's 2nd solution.

FP:
I've never found related records from a multi-line global, not sure how
that works.

42:
I am currently wrestling with whether DateOut will change or not. My
client thinks it may...rarely. And so at what point do you complicate
design to accommodate exceptions? Anyway, size isn't an issue, as
DailyLineItems are deleted as the DateIn passes.

HS:
I'd love to give FM8 a whirl on this, but it's hosted on a 3rd party
server and it's my understanding that FM8 won't be available for my
purposes for over a year.

Thanks again,
-G

Pretty easy actually. Assuming you have COMPANY and CONTACT and you
want the related contacts for a found set of companies.
Create a layout in COMPANY called "IDs" and only put the "ID Company"
field on that layout.
Create a global text field anywhere.
Create a relationship from the global to the IDs field.

When you have your found set of records in Company
- go to the "IDs" layout
- copy all records
- paste into the global field
- commit the record (don't skip this step)
- go to related records eg. global to ID Company in Contact

The values are carriage return separated in the global so FM will go to
all matching records for each item (ID).
A text field to number field relationship is not a problem here.
This also works with concatenated calc fields.
You can create relationships from that global to any IDs so that you
can always use the same global for going to a set of records anywhere.

Cool.

I just received my FM8 and FM8Adv licenses through the VLA maintenance
program but haven't installed it yet. I don't have much real use for
them except for playing around until the servers show up, although I'll
be switching to 8 Advanced for dev work immediately.

I'm surprised this wasn't mentioned as a feature. Its more useful than
several things that were mentioned.

Offhand, do you know if its fast or not?

-regards,
Dave

In article <11i6enqmbl1co60 (AT) corp (DOT) supernews.com>,
howard (AT) antispahm (DOT) fmprosolutions.com says...

In article <1126454492.064419.293190 (AT) z14g2000cwz (DOT) googlegroups.com>,
grip (AT) cybermesa (DOT) com says...
Simple

When support for the exception is added to the system requirements. (And
at that point its not an exception, its a supported case)

Let the client make the call, he will have to balance what it it will
cost to build against how often he'll need it against how he'll cope if
he doesn't have it.

Ah. Well if its a small table, and will always be a small table, the
'on-the-fly calculations' will probably not a be a signficant problem.