Component Ordering/Building

[yawny]

My client has a pretty normal retail/manufacturing business. They design and build their own jewelry, which they sell.

Orders are taken as they come in. Every week they run a set of reports detailing the new items ordered, with a breakdown of all the components needed to build those new items.

Invoices -< Line Items >- Products

and

Products -< join_files >- Components

...where components are several distinct files each representing a well-defined component type: Wires, Chains, Beads etc. They're joined in many-to-many relationships since each finished Product can have many Wires, Beads, etc. and each Wire or Bead may appear in many different Products.

Now, to produce the reports some pretty ugly export-to-temp file scripting with final importing into a Reports file has been used, all of which is hard to maintain and debug. There must be a more streamlined or elegant approach based on careful design of relationships e.g. using calc fields.

How would one go about reporting on the number of components needed for all items ordered within a given period? Which file(s) would be the logical place(s) to summarize?

(There is a further complication in that certain types of components are allowed substitutions by the customer: e.g. you can get "Ring A" with a ruby instead of the default opal. But if I can even get the main part of this problem under control, I'll deal with this aspect later.)

Thanks in advance for any pointers, sample solutions or general concepts anyone can provide.

- john

[Ugo DI LUCA]

Hi,

What you need is a relationship joinfile---Line Item based on the Product ID.

A single Portal in the Componant would list the Product ID's attached to a Product, a Portal in Product would list all components, which you probably already have.

Now a relationship from Componant to Line Item using the ValueListItem function in the Componant file (from values coming from the JoinFile) will give you a list of any product related to the component ID, with a relationship Component:ValueListItem::LineItem:ProductID.

All you need are some good calcs used with these relationship.

[yawny]

Ugo, I'm almost with you -- I think the using the ValueListItem function is the "missing link." I need, but I haven't used this technique before so bear with me.

But how do I populate the valuelist in the join file?

TIA -

john

[yawny]

Well, I got it to work although not exactly in the way Ugo suggested...

I had to define a field using the ValueListItem function in the join file, not in the component file. Then the component file gets that field thru a simple relation to the join file by component ID.

(When I tried to define the ValueListItem function in the Component file, it always gave me the same result for every component, which was the Value list for the first record found in the join file.)

Now I have to figure out how to provide the ability to override components on the invoice and still have the reporting be accurate.

Thanks...

[Ugo DI LUCA]

Hi,

I made my answer quickly, so I may have meesed up, but glad you made the junctions.

Now for this latest issue, I'd suggest you add 2 fields in your line item :

- componentswitchOut

- componentswitchIn.

These fields would hold the Ids of the component you substracted and the one you added to suit the customer need.

With this done, your Product ID doesn't change.

The the component total in line item would be your calc + sum(LineItem::componentswitchIn) - Sum(LineItem::componentswitchout).

Tell if you do't understand...

[Ugo DI LUCA]

Hi again,

Let's take it step by step.

You have relationships :

Components---Join---Products

Products----Line Item----Invoice

Assuming you have a Componant ID, you have a relationship "Joinfile" Componant::Componant from the Componants file to the Join.

Go to Define Value List in Component file and define a value called "Products".

Set this Value list to pick up the Product Id in the Join using the relationship "Joinfile"

Then create a calculation c_ProductVL = ValueListItem(Status(CurrentFileName, "Products").

This calc must be UNSTORED.

Create a new relationship from Componant to Products using the c_ProductVL at left side and the Product_ID at right side. Call this Relationship "CompToProd."

Create another relationship from the c_ProductVL to the Product_ID in the Line Item. Call it "CompToLine"

Now you can start making some calcs from the componant to the Line Item....

Count(CompToLine::Product_ID) should give you the number of Products involved using the specific componant.

As I said first, if some customers switch from the Rubi to the Emerald, then you need some switch fields populated with the Componant ID.

Create 2 new relationships from the Componant to the Line Item :

CompAdded = Componant_ID::Componant_Added

CompSubstr = Componat_ID :: Componant_Substracted

So now Count(CompToLine::Product_ID) + Count(CompAdded::Product_ID) - Count(CompSubst::Product_ID) should give you the final count of Products using this componant.

You should also look to a way to change the description of the Product according to the switches made.

Tell if you need this, as you would need some related calcs and some Subsitute Paragraph functions, but these latest are easy to implement I think.

[yawny]

Ugo,

You are very kind to take the time with this solution.

I did not implement as you describe above, in particular definition of the value list in the component file. In fact I was rather surprised to see that what I had seemed to work. Then, as I left my office, I ran into some strange problems with calculating quantities. So I will make another pass at it using your methodology as above.

If this works, then I have one last problem which should be easy, and this is to constrain the reporting by invoice date. For this I would guess to create a calc field in LineItems = product_id if line item is within the report date range. Then my relationships would link to this field rather than to the normal product_id field in LineItems. Any better ideas are much appreciated...

This forum is very cool! I really appreciate the help.

Best regards.

john

[Ugo DI LUCA]

Hi again,

How would one go about reporting on the number of components needed for all items ordered within a given period? Which file(s) would be the logical place(s) to summarize?

The files you have are enough.

There are some good tools in the sample section here for Ranges.

You would just need a Range relationship focusing on the Invoice date in the Line Item, and therefore change your calcs...hmm... using a concanation Product_ID & Date in the related file.

Or a calculation in the related file that returns 1, 0 if the date fall or not within the dates given in global fields within your componant file.

As this calc isn't stored (referenced to globals), you couldn't use it as such for a portal display, but a summary report running on 1's result would be OK in your case.

So g_start date and g_end date in the Main file

and c_checkg_dates = Case(Invoicedate < ::g_end date and Invoice > ::g_start date, 1,0).

If you really want to index this calc, you may like to script it within a found set and then use a constant one to one (1::1) relationship to display the results in portal.

Rather tedious though.

or you may only use the relationship given in my previous post, and sort this relationship based upon this calc. The 1 would come at first....

[yawny]

Or a calculation in the related file that returns 1, 0 if the date fall or not within the dates given in global fields within your componant file.

As this calc isn't stored (referenced to globals), you couldn't use it as such for a portal display, but a summary report running on 1's result would be OK in your case.

OK, I think I've been looking at this $%xc! screen too long. Can you elaborate on "summary report running on 1's result"? s'il vous plait

I had already set up global dates in the component file, and a calc in the related line items file that compares invoice date to the global dates in Components, 1 if in range, 0 otherwise. How do I "run on 1's result" from the Components file?

[Ugo DI LUCA]

Hi,

See my answer here and follow the steps. You will even get another answer, former than mine, that you can also use.

You just need to adapt the calc in the related file as the one I suggested, but you'll get it.

Otherwise, just ask for more detail. The hint with the reange in your case would be to concanate the Date and Id within a value list, so I believe this is the best answer (for the moment) I can get .

thread here

[Ugo DI LUCA]

Sorry, here is a thread more close to the case.

search empties

Once you've got the calc, you'll see the process. The calc there shows empty records, but yours would show existing records within the date range.

[Ugo DI LUCA]

Now the latest and may be the best choice would be to extract the Product from the value list and paste it in another global field with the Month, as I assume you're looking for a Monthly report.

You need

In the Component file :

a global field = g_Month Number

a calculation = Maxoccurence = Count(Joinfile::Product_ID) will give you the current number of product which uses this component.

In the related file :

a calculation c_Month = Month(Invoice Date)

a calculation c_concanate = Product_ID & c_Month - indexed.

Then a loop script.

Loop

End Loop If (globalnum = Maxoccurence)

Set field (globalnum, 1)

Insert calculated result (g_concanate, Substitute(MiddleWords(Substitute(ValueListItems(Status(CurrentFileName), "ProductList"), " ", "/ "), globalnum, 1), "/ ", " ") & g_Month Number)

Setfield (globalnum, globalnum +1)

End Loop

Back-up your file first.

I think this should lead you with a value list of type

Product_ID001 10

Product_ID002 10

Product_ID005 10

....

That is a value list that would be suitable for a relationship with your concanate field in the related file.

I just picked up Ray's calc for extracing a value from a value list, which also adress the problems for spaces in the value list.

[Ugo DI LUCA]

Set field (globalnum, 1)

Loop

End Loop If (globalnum = Maxoccurence)

Insert calculated result (g_concanate, Substitute(MiddleWords(Substitute(ValueListItems(Status(CurrentFileName), "ProductList"), " ", "/ "), globalnum, 1), "/ ", " ") & g_Month Number)

Setfield (globalnum, globalnum +1)

End Loop

[Ugo DI LUCA]

Hi,

While you're at it, and if you're looking to a Monthly report for any component used within a given Month.

Use this nested substitute calculation in the component db :

c_showAllProductsinMonth = Substitute(ValueListItems(Status(CurrentFileName), "ProductList"), "

[Ugo DI LUCA]

I was curious and went to test it.

Here are the test files.

Works like a treat...

Just a little change in the calc above should meke it work.

Component.zip

[yawny]

Ugo,

Thanks for all your scripts & suggestions. Actually the reporting is a little thornier than monthly. In essence it is done weekly, but not *necessarily* on the 7th, 14th, 21st, and end of month. It is run on demand between any two arbitrary dates.

However the bulk of what you've supplied, especially the use of valuelists to provide matching keys across many-to-many relationships, and the techniques for setting up range relationships, are really helpful. (This is probably old hat to people on this forum but IMHO Filemaker solutions to even slightly complicated problems tend to be a little nonintuitive. sorry, off topic.)

I had some issues with the component switching due to the UI in Invoices but I'll try to work out the details over the next day or two.

[Ugo DI LUCA]

Hi,

When it comes to deal with no direct relationship and what I'd call cross relationship, there are little chance to find any reference in FM Manual, as every circumstances would be different.

So the first thing to do is draw up a hierarchical diagram of the relationship flews, and identify where the information should be and where it comes from.

There are though certain limits where scripts are useful, and some where a new design would be useful.

In this example, there is no way to have a report of all Invoices from the Line Item sorted by component, except if you drop each component into separate fields in the Line Item.

Though there is no logic to have such a report in my opinion, as there are no direct link from Invoices to Component, in a Manufacturing process. There would be a direct link from Component to PO...

When you want to by-pass the logic, you'll end with portals and global fields and filter the Line Item. That's all you can do.

Your report would be used in the Component File, placing in it the Component Id, its description, and 2 portals with the list of invoices and the list of Products.

As you know portals don't print clear, you coud turn the entire Portal in calculated fields based on Nested Substitute calcs on ValueListItem Calcs and then have display the Products and Invoices this way :

Prod001, Prod003, Prod 006, Prod 007,...

Inv001, Inv008, Inv009,...

Using a basic calc

Substitute(the text field, PP, ","), where your text is the ValueListItem(Your file, your value list) and the PP is a carriage return.

If you really want to use this report within the Line Item, you coud do it Component by Component using a global field, or adding each componant into one sole record within the Line Item. Not very simple though.

Hope this helps.

[yawny]

In this example, there is no way to have a report of all Invoices from the Line Item sorted by component, except if you drop each component into separate fields in the Line Item.

Though there is no logic to have such a report in my opinion, as there are no direct link from Invoices to Component, in a Manufacturing process. There would be a direct link from Component to PO...

This is generally quite true. For (largely historical) reasons this company more or less enters everything into Invoices, which end up doing double duty as POs. It's not set up as a classical manufacturing operation basically because the company started out very small and making things to order as they came in. Also they have had a couple of people come in and mess with Filemaker over the years but don't have the money to really overhaul it properly.

When you want to by-pass the logic, you'll end with portals and global fields and filter the Line Item. That's all you can do.

Your report would be used in the Component File, placing in it the Component Id, its description, and 2 portals with the list of invoices and the list of Products.

As you know portals don't print clear, you coud turn the entire Portal in calculated fields based on Nested Substitute calcs on ValueListItem Calcs and then have display the Products and Invoices this way :

Prod001, Prod003, Prod 006, Prod 007,...

Inv001, Inv008, Inv009,...

Using a basic calc

Substitute(the text field, PP, ","), where your text is the ValueListItem(Your file, your value list) and the PP is a carriage return.

Using substitute calcs on valueListItem fields is something totally new to me but obviously very useful and well liked around here. Your sample files are great, that shows me how to use a global to filter out line items. And the substitute calc sure seems to work.

The last non-trivial problem (which might change the whole design) is to calculate true quantities of component based on quantities ordered--as each Product may contain 1, 2, 3 or 4 of each component (e.g. 2 rubies in the "Fire ring"). Of course this component quantity is stored in the ProductsToComponents join file for each product/component pair.

I'm starting to think that I should just use calc fields in the line items file to store component1, c2, c3, and c4; and c1_quantity, c2_quantity, c3_quantity and c4_quantity. (The calcs would be based on value lists in components...)

That doesn't seem too elegant to me, in fact kind of brutish, but printed reporting would be a lot easier. hmmmm

[Ugo DI LUCA]

Hi,

Go in the sample section and download the "Inventory for set test file". You'll find some clues for that answer. If you need to count both products and row products, you'll probably end having the same product file for componant and products....

Therefore, your inventory count will be based upon those entries in the line item for both products and components.

Your design having PO and Invoices all along the same line item is OK as long as you have no link from a PO to an Invoice...

[yawny]

Actually if I went the route of stuffing component data into each line item record, I think I would want to build a value list in LineItems based on a concat field in ComponentsToProducts (productID & quantity). A value list based on quantity alone will fail since not every entry will be unique.

Then calc fields in lineitems could pull out component quantities and IDs using string calculations.

This all seems kind of neanderthal though...

[Ugo DI LUCA]

Hi,

Are you saying that each component from a product are looked up within each line item along with the Product_ID ?

How many components do you have ?