Timebased relationship for production. Is it possible ?

[LooserX]

Hi 

 

Im new to FM and database thinking, so please be gentle with me  .

 

Im still evaluating FM 12 and haven't yet decided if it is the right way to go. But from what i have seen so far, it has everything we need + a lot more, so it is only this "application" left to sort out. 

 

My goal with the database is to create a simple quality system for a manufacturing process.

I want to log the type of materials (and their batch numbers) used for certain products.

I have searched and searched, but i still haven't figured out how to do it. Probably because it is so easy,  i cant see it.  

 

ill try to explain the process. And give the desired outcome of the data

 

I use many materials that have a limited shelflife. I make many products of that materials. The products all have different startdates and enddates, and might span over many batches of materials.

 

For Example  

 

MATERIAL  BATCH #    InDate

Material A   BatchNo1   1.1.2013

Material B   BatchNo1   2.1.2013

Material A   BatchNo2   2.1.2013

Material C   BatchNo1   3.1.2013

Material B   BatchNo2   4.1.2013

Material C   BatchNo2   5.1.2013

Material A   BatchNo3   6.1.2013

 

PRODUCT  STARTDATE  ENDDATE  

 

Part 1           1.1.2013        3.1.2013

Part 2           3.1.2013        5.1.2013

Part 3           4.1.2013        4.1.2013 

 

By logic i can say that in

- Part 1 i have used "Material A BatchNo1" and "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" 

- Part 2 i have used  "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" and "Material B BatchNo2" and "Material C BatchNo2" and "Material C BatchNo 2"

- Part 3 i have used "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" and "Material B BatchNo2"

(since i am writing this so everyone can see it, there is probably something wrong with my logical calculations now    )

 

But my problem is that i cant get FM to get my desired answers..

And is it even possible to make this kind of database..

 

Any help would be much appreciated

Thanks 

 

[Wim Decorte]

You have not told us  yet what the "desired answers" are... I'm assuming it is the product start/end date view?  Do you already have the material/batch view working

we'd also need to see what tables you already have in this setup.

In your example I do not see a start date anywhere except in the desired end result, where do you record that?

 

I get the relationship between parts and material/batches.  That material/batch table is what we would call a join between a separate materials.

So i'm seeing thinking these tables?

- parts

- materials

- batches

- material/batches

[LooserX]

Hi..

 

Sorry..  Its so obvious for me since i'm in the middle of it..  

The desired answers is the logical answers i wrote in previous post..  ( Part1 is made of Material A, Batch1, Materal B, Batch1...... and so on )

 

I have 3 Tables, and 1 report layout

 

Table 1( MaterialUse)  has 4 fields  (ID, Material; BatchNo, Indate)

Table 2 (ProductionStart) has 3 fields (ID, Part, StartDate)

Table 3 (ProductionStop) has 3 fields (ID, Part; EndDate)

 

The only relationship i am sure about is Productionstart::Part = ProductionStop::Part

 

Simplified, the workflow for production is. 

 

1 Material in,  the worker fills the "Table 1" (Material; BatchNo, Indate)

2 Production of the Part1 begins,  the worker fills the "Table 2" (Part, StartDate)

3 If needed, more material is brought in,  the worker fills the "Table 1" (Material; BatchNo, Indate) with the new material and batch#

4 Production of Part2 begins, the worker fills the "Table 2" (Part, StartDate)

5 Production of Part1 ends , the worker fills the "Table 3" (Part; EndDate)

6 Maybe more material is brought in.  

And so on..    

 

So material and parts will overlap each other freely.  

 

So, in the report i want to be able to see each part. Its startdate, enddate, what materals is used in the part (including batch) and material InDate.

 

Thanks for taking the time to help me :-)

 

Im not allowed to upload my file :-(  But i hope my explanation answers some question..

 

Thanks 

 

/ LooserX

 

 

 

[comment]

By logic i can say that in

- Part 1 i have used "Material A BatchNo1" and "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" 

- Part 2 i have used  "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" and "Material B BatchNo2" and "Material C BatchNo2" and "Material C BatchNo 2"

- Part 3 i have used "Material B BatchNo1" and "Material A BatchNo2" and "Material C BatchNo1" and "Material B BatchNo2"

 

I am afraid I don't quite follow your logic: it would be very easy to list the batches that were brought in during the production of a part - as in Part 1 of your example. However, Part 2 uses batches that were brought in before the production started - how exactly can we determine this, and how far back should one go?

 

Part 3 is especially confusing, because "Material B BatchNo2" has been brought in on the same day the production started - yet you say you have also used "Material B BatchNo1" from 2 days back. I don't see anything in your data that would allow us to determine if that was indeed the case.

[LooserX]

Hi..   Thanks for helping me out here :-)

 

For your first question.

 

I am afraid I don't quite follow your logic: it would be very easy to list the batches that were brought in during the production of a part - as in Part 1 of your example. However, Part 2 uses batches that were brought in before the production started - how exactly can we determine this, and how far back should one go?

 

We must assume that we use a little bit of Material B batchNo 1 in Part 2 since Material B BatchNo 2  was brought in in the middle of making Part 2.

Think of it like painting a room, and you only have small buckets of paint..   You will use maybe 2,5 buckets for the first wall. Naturally you wont throw the remaining 0,5 bucket of paint away, but you will continue painting on the second wall.  So in that case walls and buckets of paint might overlap eachother quite freely. And we only need to know the previous bucket of paint. 

 

 

 

For the second Question.

 

Part 3 is especially confusing, because "Material B BatchNo2" has been brought in on the same day the production started - yet you say you have also used "Material B BatchNo1" from 2 days back. I don't see anything in your data that would allow us to determine if that was indeed the case.

 

 

Its a little bit of the same scenario as the previous question. I could change the Material_Indate to be a Timestamp, Then the "same date" scenario newer would happen. We might have used Materal B BatchNo1 (like the 0,5 buckets of paint) or not..  There would never be a "same date scenario".

 

/  LooserX

[comment]

We must assume that we use a little bit of Material B batchNo 1 in Part 2 since Material B BatchNo 2  was brought in in the middle of making Part 2.

 

 

Well, this assumption does not apply to Part 1 in your example, so that's what confused me. Anyway, this is not going to be simple. Let me ask two more questions to try and determine the best path to proceed from here:

 

1. How does one know which materials are required to make a specific product? Will you have a "recipe" for each product type?

 

2. In what form do you need this result? The easiest way to produce it would probably be on-demand for a specific part - i.e. select a part to see the "log" of batches that went into it. However, this way you won't be able to produce a report for more than one part at a time.

[LooserX]

Hi.  

 

Answer 1.  Yes. we have a "recipe"   for each part, so in case of a "worst case scenario" we will have to manually look at the data and sort out exactly what material that went into the part. But for most of the time it is sufficient to know what material that has been at the workingplace while the part was made. .

 

Anwer 2. I hope that it is possible to see On-demand for specific part,  and to see On-demand for specific batch. In an ideal world it would be nice to see all material and parts used/made between dates(doesnt have to be sorted).

 

/ LooserX

[comment]

But for most of the time it is sufficient to know what material that has been at the workingplace while the part was made. .

 

I am afraid it's not as simple as that (or perhaps I am still misunderstanding the situation). I see two steps here. The first step is relatively simple: locate all batches that have been brought in during the making of a part. This is easy because it's only a matter of identifying batches where:

 

Product::StartDate ≤ Batch::InDate ≤ Product::EndDate

 

The next step is not easy at all: for each material required to make the part, you must locate the most recent batch taken in before Product::StartDate. Suppose there are 50 kinds of materials altogether, but only 3 of those are required to make the part in question; in the absence of "recipe" you would have to hunt down 50 batches - and you could end up with batches brought in 6 months ago that have no relevance whatsoever to the part in question.

 

The recipe for this purpose could be as simple as a checkbox field of required materials (with 3 out of 50 materials selected in this example). Alternatively, you could limit the scope of "the most recent batch" to say, no more than 30 days before the start of production.