Hi, new posting here, but come here often to look up solutions. I
have a complicated system of relational DB's (in FMP6)and thought I
had figured a solution to running a summary/comparison report.
Unfortunately it is not working completely. I have a PO db linked to
PO Line items. Then I have an Invoice db linked to the PO db, and to
an Invoice Line Items. What my manager wants is to link the PO Line
Items to the Invoice Line Items.

Basically a PO has many line items (usually only one part #) but
different shipping dates and quantities. Then the Invoice is written
when items are shipped, (usually only one line item) but it could
consist of more than one PO line item. Ex: PO #12 has 3 LI's: 1)
part 1 ship date 7-1-03 qty 100
2) part 1 ship date 7-15-03 qty 200 3) part 1 ship date
7-30-03 qty 100

Invoice # 023 has one line item: 1)part 1 Actual Ship Date 7-3-03
qty 75
Invoice # 024 has one line item: 1)part 1 Actual Ship Date 7-20-03
qty 225

as you can see invoice #024 has some parts from PO LI #1 and PO LI #2

what my manager wants to see is that on PO #12 Total po line items and
total invoice line items (which I can do) but then she wants to map PO
Line Item #1 to the Invoice(s) to look like:
Po# Date promised Qty Date Actually shipped Qty shipped

Which may have more than one line of Actually shipped dates for each
Po Line item.

I currently am using a subsummary part report layout that is based on
the PO# and then sorts by (related field) PO line Item in the Invoice
Line item DB. I was able to connect the correct line item to the
corresponding line. But when I run the script to sort the report, it
does not show the 2nd line item in the invoice. the totals (summary
fields) are correct, but the body is not showing the 2 lines.

I hope this makes sence to someone. I have been racking my brain,
books, and this web site to find out what I have done incorrectly.

Any help would be greatly appreciated.

Thanks!!

Lisa

Caliko <calikothom2000 (AT) yahoo (DOT) com> wrote:

haven't been doing databases for a while and am getting hazy. You could
try this on a copy of your file:

First create a calc field in your InvoiceLinitems file = PO number from
the relationship with the invoice file. This gives you the related PO
number in your INvoicelineitems file so you can use it.

Now create another calc field = a concatenation of this calc and the
part number from the InvoiceLineitems file. (You could make this a text
field with a few spaces between the PO number and the Part Number to
avoid confusion with similar numbers.)

In your POLinitems file create a similar concatenated field which
combines the POnumber and the part number.

In the InvoiceLineitems file create a relationship between these two
fields to the POlineitems.

Now on your report layout in your Invoice Lineitems file create a
subsummary part which is sorted on the concatenated field you created.
In it put all the PO stuff from the recently created relationship.
In the body put the lineitem stuff.
Sort on the Concat field followed by whatever you like (date shipped or
invoice number perhaps) to get a report which shows each part for the
current PO, followed by the various invoice lineitems which have
fulfilled it. (You could have another subsummary below showing the
balance remaining to be fulfilled on this PO/Partnumber.) The next item
will be the next part number which was ordered in that PO, with the
various corresponding invoice lineitems in the body.

Not sure if this is what you're after. You could have a report where the
PO info was repeated on every line, but I think this is clearer.


Maire Black