one report for different fields containing the same value

[zinim]

Hello...

I'm quite a beginner so I hope I even defined the title correctly.

I have a table of products (clothes), a table of Fabrics and a Table of Orders. each product can be made from more than one materials (fabrics). I've defined a portal in the Product table with Fabric1, Fabric 2 etc. and Meters Needed from each Fabric.

Now I'm trying to create a Fabric sorted report for the quantities of fabric needed, and looking for a way that I could unify the values in the different Fabric1(2)(3) fields into one field since Fabric2 of one product is actually the same fabric as Fabric1 of a different product.

Hope you got me, if not sorry...

thanks for any help,

Nimrod.

[Fenton]

You could possibly write a long-winded calculation. But really, this problem is caused by poor relational structure. If you have a portal into the Product-Fabrics from a Product, then why don't you just have 1 Fabric field, and 1 Meters field, which a different row for each different type of fabric? Why do you need a Fabric1, Fabric2, etc.?

These non-relational flat methods, which seem easy to set up, therefore attractive to beginners, make it difficult to use or report on the data.

[zinim]

Thanks for the reply... I think I got you but, maybe I wasn't clear enough. Each product is made out of different fabrics in the same product. these are not different fabric choices for one product. The same "dress" contains in itself both Fabric1 and Fabric2, then again the SAME "dress" might contain (in a different choice) Fabric3 and Fabric4...

I think this is why I can't apply your solution... right ?

[Matthew F]

...you've got me totally confused. Apparently you have a relationship between Products and Fabrics. What is the match field? What are the names of the fields in the portal? What is it that you are trying to calculate?

[zinim]

Firstly, Thank you. I'm not a pro and maybe I should submit to a proffessional... but I'm having fun for now (should I ?)

now to set everything on the table, and maybe you could help me solve this one.

I have:

1.An Orders Table related To Products Table through an intermediate Table, matched by OrderID and ItemID. Where each order contains few products with different color choices (same Itemcode).

2. Product table related to Fabrics tables through an intermediate table, mathced by ItemdID and FabricID. Where Item's different color is made out of more than one fabric. in the Product table I have a portal with a color|fabric1|meter1|fabric2|meters2|fabric3|meters3.

I am trying to get a report for how many meters needed from each fabric. and the problem is that the same fabric can be a value in a different field. for one color choice the fabric can be in field fabric1 and for a different color choice the same fabric can be in fabric field fabric3.

If all this still doesn't make sense, so I'll try and build my structure differently. thanks for all your help.

[Matthew F]

No need to apologize. I'm not a pro either, I'm an academic. Your problem looks easy but its a little trickier than it seems. The use of 'intermediate' tables (aka join or hash tables) is a good idea because it allows you to handle many-to-many relationships.

... in the Product table I have a portal with a color|fabric1|meter1|fabr ic2|meters2|fabric3|meter s3.

You should do away with the fabric1, fabric2... type of design. Instead use two more tables, one called Items to store the fabric data and another intermediate table to link to the Items with Products using 'ProductID' and 'ItemID'.

Your relationship graph will show:

Orders >= OrderProductJoin =< Products >= ProductItemJoin =< Items

Define the following fields:

1. OrderDate (Orders table)

2. ProductNumber (OrderProductJoin table)

3. ItemNumber (ProductItemJoin table)

4. StartDate [Global] (Items table)

5. ItemCalc = If[item::StartDate < Order::OrderDate ; ItemNum * OrderProductJoin::Quantity ; 0] (ProductItemJoin table)

6. ItemSum = Sum(ProductItemJoin::ItemCalc)

Place a portal to the ProductItemJoin table on the Items layout and include ProductNumber, ItemNumber and ItemCalc fields from their various tables.

Place the SumItem field somewhere on the body of the Items table. Enter the cutoff date in 'StartDate' and do a find with '>0' in the ItemSum field to generate your reports.

That should do it! Attached is an example file.

P.S. Attached is an example. Look at the Items table to see the calculated result.

fabrics.fp7.zip

Edited December 4, 2007 by Guest
Re-uploaded attachment.

[zinim]

Thanks, I can't get the attachment though.

[Matthew F]

Sorry. I uploaded it again. Hopefully the upload worked this time.

[comment]

If I'm reading this correctly (and that's a big IF), you need one more table for this: