Parents, Children and Grand-Children reports ??

[Ugo DI LUCA]

Hi all,

I'm just wondering how you are adressing this need ?

Say you have Order, Line Item, a Product, a Parts and PartsInProductsJoin files, and you want these parts to appear in the Order Form for the guys in the warehouse to prepare the goods, starting with the parts for each products.

I've somehow solved this for one guy here a while back by introducing the parts needed to the Line Items at the moment of the order. This involved a looping script and a set of different relationships from the Order to the Line Item.

How this would be best solved ?

- A separate file

- The one I'm using ?

- Portals, even though limited because of the Print procedure ?

- Other technique ?

Thanks for any input.

[Kurt Knippel]

What are the relationship? Is an order made up of products? Is an order made up of products & parts? Or does parts just define products?

If an order is made up of both parts & products, then I would keep them in the same child file. Sometimes people get hung up on names and think that because one is called a product and one is called a part that they must reside in different tables (even if a product is made up of many parts).

As an example, the company I work for is in the architectural/construction business. We have a table of Construction Assemblies such as Roofs, Sloped Roofs, Roof Ridges, Ridge Tiles, Wind Clips. Now what I just described is actually a series of parent-child relationships, but all exist in the same table, because at thier core they are all just different Construction Assemblies, even though we actually call them Packages, Assemblies, Sub-Assemblies and Parts.

Now if parts are simply elements of a product and not dealt with seperately, then I would keep the structure that you have now. Do not actually give them thier own line item record, but just have a "Parts Needed" field in the products record. This could even be calculated dynamically in the Products table by the use of the Status (CurrentValueListItems) function.

[Ugo DI LUCA]

Yes Kurt, you're right. I just wanted to make it a more global querry, but I should have brought this precisions.

Actually, the parts could be sold separately, so the structure I'm currently using is :

Products.fp5

ProductsInSetJoin.fp5

There's in fact no Parts file in my structure, and items that could be sold either in a set or individually are "marked" exactly as you're doing.

CustomerOrder.fp5

Delivery.fp5

Invoice.fp5

LineItems.fp5

Now, my goal is the following...

When a Customer comes to pick his order, as the sets may or not be already prepared, I must give the Warehouse staff an Order Preparation sheet where they will find all the "Parts" and Products needed for this Order.

As Halloween is approaching, let say this order might be :

1. Witch Set

2. Eyeballs

3. Michael Meyers Mask

4. Sexy Nun Costume

5. Blood Capsule

While what should be printed would more likely be

1. Witch Set

1.a. Witch Hat

1.b. Witch Brush

1.c. Witch Mask

2. Eyeballs

3. Michael Meyers Mask

4. Sexy Nun Costume

5. Blood Capsule

To render it a little bit more tedious, the same part could be sold independantly in that same order, and some the quantities of "parts" must be accounted for Inventory purpose.

So that taking this example above, I could have an Order with :

1. Witch Set

1.a. Witch Hat

1.b. Witch Brush

1.c. Witch Mask

2. Eyeballs

3. Michael Meyers Mask

4. Sexy Nun Costume

5. Blood Capsule

6. Witch Hat ----> sold twice in this same order.

or more likely to be what I'm dealing with :

Product - Quantity

Witch Set - 1

Witch Hat - 2 (I know it's stupid, just as an example)

Witch Brush - 1

Witch Mask - 1

Eyeballs - 4

Michael Meyers Mask - 1

Sexy Nun Costume - 2

Blood Capsule - 10

Witch Hat - 2

Finally, I want this "Order Preparation" to appear sorted by Products, so that the final sheet would look like.

Product - Quantity

Blood Capsule - 10

Eyeballs - 4

Michael Meyers Mask - 1

Sexy Nun Costume - 2

Witch Brush - 1

Witch Hat - 4

Witch Mask - 1

Now, as I said, it's currently running, but having these items dropped in the Line Item seems to be somehow tedious and when invoiced, not needed anymore.

My inventory could run with scripted calculations involving either the Line Item and the ProductsInSetJoin to evaluate how many items have been sold. This mean I could therefore delete the "Grand-Children Items" that have been brought to the Line Item, when the Invoice is paid.

I'm just hesitating to re-adapt the structure with a new File, just for Print purpose, and wondering if there's another way I'm not aware of of showing GrandChilds in a report.

Thanks for your contribution Kurt. I hope this is clear.

[Charles Delfs]

I'm currently working on a project that can have many grand /great grand/ great great grand parents etc. What I did was to keep the whole family in one tree 'er in one table (file) and create self relations to the kids; This works well becuase you can do a find on the InvoiceID and then sort,summerize & report any way you want (even by location in the warehouse)

Not only line items and thier kids can be kept in this file but depending on your needs for reporting you can keep the invoices there to. I have done this by puting in a field that indicates what the exact record does.

eg:

RecordType (text) = "Invoice" or

RecordType = "LineItem"

for fields like addresses and order dates that are not used on the line items records, I just leave the field blank.

When the report is printed the header and summary parts can have the address and order date fields etc. and the body has the line item fields.

Charles

[BobWeaver]

It seems to me that the Moyer and Bowers book has a fairly detailed example of working with products/parts. If you don't have the book, you may want to get hold of a copy.

[Ugo DI LUCA]

Hi Charles,

Seems like what I'm doing too, involving some "alternate relationships", to show or not the related children.

But I doubt I could account any inventory move without involving a separate table to set each "packages" and their respective quantities of "parts" per "pack". The SelfJoin and a ValueListItems would help to identify which part is being used, but not how many there are in each set.

Am I missing something ?

As for the Warehouse location, it's a plus I had in mind too so that the guys would have a "Order Preparation Guide" as well.

In order to have those parts sold individually sorted *with* the items, I'm using a calculation then sort by this calc. The relationship from the Product File to the Line Items also use this newly created calculation.

Again, I'm still wondering what could a separate file offer compare to having a bunch of "unused" records in the Line Item file.

[Charles Delfs]

Here is a demo I whipped together (well not really whipped,took me a bit). It has the line items and thier parts all together; have a look, I think it will do what you want (And a thousand other people want too)

I did not include a sample address part of the invoice becasue I figure you can add that in. The real main idea is a recursive relationship(done by tricking FMP to over look the circular error trap.

Charles

multiitemsdemo.zip

[Ugo DI LUCA]

Great sampler Charles.

I love this Forum and the way others spend time in adapting a demo for a given situation. Thanks.

I had never used this "Notcircular" dummy field before, while I'm a "strong" lover of recursive relationships.

You can get a "test file" of the basics of the structure I'm currently using here .

It is very close to what you've demonstrated here, except it involves 3 files

The Extended Quantity would be calculated using "recursive" related calculations from the Product File, The PartsInProducts File and the Line Items. While this ugly test file uses cross-related calculations to update this Extended Quantity and Inventory count, they are scripted on each entry into the Line Items in my solution.

By reference to the PartsInProducts join, any added Parent Quantity sold would update the Grand-Children Quantity sold and Inventory. This mean I can get the Inventory count even if the Grand-Childrens, aren't introduced in the Line Item.

Introducing them there was quite only dedicated to solve "Prints/Reports" needs.

Each Order Preparation print is currently scanned and archived (as paper). I keep track of who prepared the order in the Delivery File, and use a daily script that deletes the Great Children from the Line Item at Date(Month(Invoice Date)+1, Day(InvoiceDate), Year(InvoiceDate)).

As I really don't like these delete steps, I asked if there were other ways of doing this, and if there were some concerns in doing it this way. I didn't received any advise on this issue, but since you've finally offered such a great "Recursive Theory" Demo, I have really no regret.

Writing out the problem helped form my thinking and brought me to new perspectives, along to the other related information provided on this thread.

Thanks a bunch.

[ernst]

Hey Charles,

Multiitemdemo.fp5 makes Filemaker crash at my end (actually I get the spinning beachball of death).

Which Filemaker version did you use to make it? I tried FM6.0v4 en FMDeveloper 6.0v3, both om MacOS X 10.2.6, both with the same result.

Regards,

Ernst.

[Ugo DI LUCA]

Old fashioned OS 8.6 with old fashioned FM5.5 ran correctly on this new fashion "Recursive Relational" file....

[ernst]

The file is probably so recursive that my computer can not handle it.

Actually I went back to old fashioned MacOS 9 and tried there with both FIlemaker 5.5 and Filemaker 6.0 and no problemo.

Strange...

Ernst.

[Lee Smith]

So is it OS 10.2.6 that is the problem?

[ernst]

Lee Smith said:

So is it OS 10.2.6 that is the problem?

Just retried using the mighty Panther (10.3) and a freshly installed copy op Filemaker with the same result. So it could be either OS X in general or Filemaker for OS X in general.

Heaven knows I should have opted for 'cheap PC with windows'.

Ernst.

[Lee Smith]

You would think that Apple could a least make their own software work with their own stupid systems.

Everytime I think about spending the big bucks to upgrade to system 10.x, something like this comes up.

Lee

[bruceR]

Since 100,000 other FileMaker documents work just fine, why not fix the file instead of complaining about the operating system?

[ernst]

-To Lee,

My feeling is that it is actually Filemaker that's scr*wing up, not the OS.

When I tried the OS 9 version, I was actually running it in Classic, the emulated OS 9 running within X.

- To Bruce

Have a look at the file in question. There is really not so much that can be wrong, or it must be the trick to avoid the circular structure.

Regards,

Ernst.

[Ugo DI LUCA]

There may be something in that file though, or all demos running this technique would fail under MacOSX.

Actually, I wasn't that sure to haven't come accross such calculations, and run a little search on the numerous tips I had downloaded from here and there. I've finally find several demos in my "Calculations" folder, the newest being Ray's 'CalculateDown' .

Would it be that Ray's demo won't work on your OSX based computers ?

[Charles Delfs]

The demo was created on a win-xp pro machine with developer 6.0v4

[Charles Delfs]

Ugo;

I just had a look at the CalculateDown example. MY techniques is quite a bit different.

The Calcdown is simpular to a time line or raidroad analogy, my technique is more like a book shelf, in each book there can be chapters, then paragraphs then sentences and so on. Aside from the fact that my particular file is picky on who opens it (LOL) it is still a cool idea.

I am using it in a tutorial authoring database where the author will create steps on teaching something and each step can have sub parts and so on.

Charles

[Ugo DI LUCA]

Charles,

I was just comparing the ending workaround whic consist in by-passing circular definition....

It still is a good solution, as far as it is given for sure that this isn't what brought thse OSX users to that bug.

[Charles Delfs]

Oh,

I should note that the "inside trick" to bypassing the circ error is to create the "NoCircularExQty" Filed first with a temp caculartion of say "1" then create the "Extended Quantity" field then go back and change the "NoCircularExQty" to calc the other field. Doing it this way causes FMP to over look the error, it will catch the error the other way.

Charles