orderlineitems.db5 related to products.db5 by product#.

Each product has a list of "parts.db5" that make up that product (related by product#). I'm trying to get a sorted (and grouped by part type) "parts" list for an order. Logic would be FOR each product entered in orderlineites.db5 of a given order list all parts.db5 that would make up that order. (parts sorted by sortcode in parts.db5 and grouped by product type).

Thank you for your help everyone (esp Ugo,danjacoby,pupiweb,lee,Peter,broberts If this keeps up I'm putting you on my Christmas card list)

have a look at subsummary parts on filemaker help.

use subsummary in parts.db for printing, portals in order.db for data entry...

Ok... there are four databases involved

Orders.db5

orderlinedetail.db5

Products.db5

Parts.db5

I need a list of all parts grouped by part type (field in parts) for any given order. Which db do I start the report in and how?

(I did get fm techno knowledge from filemaker, I have not found this base very helpful at my level, maybe I am using it wrong)

My guess is that you're selling a product and purchasing parts.

So there is no direct relationship from Parts to Customer Order, while there should be a relationship from Parts to Products, through a Join File (Line Item).

So you need to "virtually" transfer the Parts related to the Product into the Order Line Item.

The virtual link could be the ValueListItems calculation here.

How stupid I am.

Check this thread as you'll get all the answers. There is even a quick test sample to see it works.

Make a search for "Components" on the Forum for the last 2 weeks. I'm lazy for the link

God help me. I don't know what a join file is and none of my books have it in the index. I'm reading Ugo's previous posts on components and thinking that I might apply for that job at burger king.

Jonathan

God....hmm..not sure he knows what it is

Books...They surely use the term "Join File"

Look at your proper files. I'm quite sure your OrderDetail is a Line Item.

And how do you add component to your products. Is it in the Product file itself with repeating fields ?

ugo,

Book is steve Schwarts fm 6.0 bible. Index shows no join, filejoin etc under index, relationships, files, scripting. It does talk of "self joins" where a relationship is made between a db and itself. Not same I don't think.

Yes, compenents.db are line items in Prodcuts.db. Related by productID

NOT a repeating field, portal.

Yes, orderdetail.db is a line item in orders by orderID, portal.

Example:

The product 10x10TENT is made up of components: 4 Tent poles, 1 Canvas top, 8 stakes, 1 sledgehamer, 1 ground cloth.

The components lineitems actually derive their data from lookups to Parts.db which is the initial database holding the inventory.

When an an order line item is created for 10x10TENT it creats line item in orderdetail.db that relates by OrderID and looks up the relevent data from Products.

I then need to create a warehouse load sheet for the order that takes ALL of the PRODUCTS in orderdetail.db line items and accumulates ALL the COMPONENTS that are associated with those Products(related by productID) needed for those products and then prints them out GROUPED by the groupcategory specified in components.db (ie all tent poles in group, all the food supplies in a group etc.

Ugo, thank you so much for your help. I like france again.

Jonathan

Well, I'm Italien living in France, but I LOVE France too...

Yes, compenents.db are line items in Prodcuts.db. Related by productID

NOT a repeating field, portal.

a portal yes, but from where is coming the datas in the portal ? It should correspond to the ComponentToProduct.fp5 in the sample you downloaded. Is it ?

I think you're looking for a report that lists all components and the orders they are related to. You would surely want this list to appear within a selected range of dates or orders numbers.

Is it what you're looking for ?

True this is a complex setting, but can be done. You may like to mail me or send a PM, as the thread could become very large.

Ugo, Thanks to you now I like Italy and France!

(Actually I

Hi Ugo,

Here is what I have done as I translated your instructions

CREATE VALUE LIST

1. Went to Parts.db and selected filedefine value lists

2. Named the new value list

Hi Jonatahan,

Yes and No.

Copy/Paste this calculation if you named the value list "product lust".

c_productlistKey =

ValueListItem(Status(CurrentFileName), "product list")

Next the relationship used by the portal and its fields would be :

Tbl_parts.fp5:c_productlistKey::Orderdetails.fp5:Product_ID.

Well, at first checks that the c_productlistKey is unsorted and drop the field on the layout. You should see a list of Products in the field.

If this works, you may have learned a workaround to display related records a few relationship away. This would be useful for this case, and also in others in your solution.

Returning to your main concern, I prefer to create another post for this beacuse I would like to have some feedback about it from other users too.

Edited by Ugo. Before goind into details, let see if you catched the ValueListItem "trick"

Doing it now... but Ugo... do you really mean product LUST.. perhaps you've been looking at too many Jaguars drive by.

Yep ,

Esprit Latin suggested I post my files. I tried but don't see how to post multiple files and when posting one I receieved an error saying extensions were wrong.

PUt your files in a folder and zip it. Or sit if you wish.

Now the size here is small.

Now you can mail me your files directly (no zip required if they are small).

Ciao.

Do you like Esprit Latin. This is the current name of my company. Spirito Latino, Latin Spirit.

Latin Spirit FM Solutions . How does it sounds ?

The "Dumbo" solution I used on my old database was to create a "holding" file for parts and a query that rougly said "For each linedetail, Find all components matching same code, enter a record into the temporary file. So all components were dumped into that bucket along with the sort codes. Then a separtate querry did the sort and print... then dumped the bucket. I did that in the old database but am not quite sure how do the same in the new system (although the relationships are basicly same). OR if there is a simpler solution.

J

Jonathan,

As noone gave another solution, here are the steps for the scripted method.

As I said, in order to display and print a subsummary report as the one you want, we obviously need the fields that are missing in the Join File Orderdetails.fp5.

So...

You should already have :

Relation 1 OrderMain.fp5:Order_N

Ugo... I'm working on your solution, a couple of clarifications as I've put it in.

<g_Constant (global = 1)>

? How do I set this at 1... don't see mechanism in field create.

Script 1 in OrderMain.fp5 :

SetField (g_OrderN

Hi,

<g_Constant (global = 1)>

? How do I set this at 1... don't see mechanism in field create.

---> Define the value as a global. In browse Mode, set it to 1 (not in define fields)

<g_Constant (global = 1)>

? How do I set this at 1... don't see mechanism in field create.

---> Sure.

Set Field (g_Product_ID, Product_ID)

Go to related records (Relation4::Product_ID)

? checkbox "show only related" yesno

? Was not able to spec Product_ID here... was that an error?

---> Product_ID is my generic term for your Package ID. Choose your fieldname for package.

? button in WHAT portal row. I'm assuming ORDER main has a button "Print Load report" to run scripts... only portal in Order is for line items :

---> The script I suggest isn't a print script. It's a "export record to line item script".

You would like to put it in the first row in layout mode. It would appear in each row of portal then.

When it's done, I'd tell you a better approach to would check if the script has been triggered, using some container trick.

?Do I use layouts --> new layoutreport --> Columnar report to produce report.

----> YES...But let see if it works first.

If so can that relationship work "on line" with somebody like myself.

---> I don't get it ...

--------------JW REPLY

Ugo,

----Question 1 ---------

In these two scripts

<<Script 1 in OrderMain.fp5 :

SetField (g_OrderN

did you put in the fields only as a reffence?

Correct. As I said, I didn't tested it. I just typed it quickly and got confused with these GTRR. Sorry for that.

Try to understand the steps of the script, as you could debug it if it happens.

The script will be trigered when you have finished entering a new product in the portal.

It will go to the Line Item, at the specific related line that is references the row in which you clicked the button.

There, it will set the globals (OrderN

Yes... so the "button" that performs "script 1" (and cascades through the other scripts) is placed in the Orderdetail file and has to be visable on the line??? Does this have to be pressed... is it actually a button or where is the function that requests the trigger upon departure of line item?

I am getting closer to understanding this. Thinking Cap is ON.

Jonathan

Just to conclude this thread let it be known to all men that Ugo's solution above worked for me after some tweaking. Whoever is responsible for passing out Gold stars please send one to Ugo.

Jonathan