Portal showing one-to-many relationships

[jjosh12]

I am building an Manufacturing Order Process. I have a Product_order_form.db or order.fp5, and a Product_manufacturing_order_form_5.db or Man5.fp5 (order.fp5 & man5.fp5)

What I need to have happen is for the user to be able to enter more than one item with its coresponding data fields on one invoice (order.fp5), but for it to automatically create a manufacturing order (man5.fp5) for each item with its coresponding data fields. i.e., <item_no> <qty> <lot_not> <prod_description> <price>.

Any Ideas?

Thank you in advance for any input.

Josh

[Kundinger]

Hi Josh,

Here is the basics of what you need...

order.fp5

[Ugo DI LUCA]

Hi Josh,

Is it that each item ordered has some parts associated and you want to run either :

- an Order with items

- a Production order with items and related parts.

If this is the case, it could happen to be far more tedious to set, but not impossible.

[jjosh12]

I understand your concept. I just dont know quite how to do that.

How do you make the many relationships from order.fp5 and how do create a lookup from man5.fp5? I dont need step by step but would you mind giving a little more detail. Im sorry to ask.

Do I have the fields that will be multiple in the order.fp5 repeating and relate them to the item.fp5 (is this the same as a line item?) and then create a lookup script?

thank you

josh

[John Caballero]

I'm not sure why you need to duplicate your information in Order.fp5 into Man5.fp5, but here goes.

Create a LineItems.fp5 database. The data in this table will be shared between your Order.fp5 & Man5.fp5 tables.

In each of your tables, in addition to whatever fields you need, create these fields: cConstant, nID, gID.

cConstant is a calculated field that returns the number 1 (in other words when in the specify calculation box, simply enter the number 1).

nID is an auto-enter serial number.

gID is a global text field.

In Order.fp5 create an nMan5ID number field.

Create a constant-to-constant relationship from Order to Man5.

Create a constant-to-constant relationship from LineItems to Order and from LineItems to Man5. (A constant-to-constant relationship is a relationship that is linked by the cConstant fields.)

Create a relationship from Order to LineItems that has nID (in Order.fp5) on the left side of the relationship and nOrderID (in LineItems.fp5) on the right side. I specify both the name of the related file and the matching keys in my relationship name, so I would name the relationship: LineItems_nID_to_nOrderID. This way if the relationship ever breaks, I can quickly redefine it because I know how it links.

Create a relationship from Man5 to LineItems that is linked by nID in Man5.fp5 to nMan5ID in LineItems.fp5 (LineItems_nID_to_nMan5ID).

Create a relationship from Order to Man5 that has nMan5ID (in Order.fp5) on the left and nID (in Man5ID) on the right. (Man5_nMan5ID_to_nID).

OK, so in Order.fp5 you'll have a layout that has on it a portal to LineItems.fp5 which is based on the LineItems_nID_to_nOrderID relationship.

You'll have a button called New Line Item (or something like that).

As an overview, here's what will happen when you click the button:

You'll set the nID field value of the current record in Order.fp5 into the gID field in Order.fp5.

If there isn't a corresponding record in Man5.fp5 (you'll need to check for that), you'll create a new record in Man5.fp5 and set the nID field value of the newly-created record into the gID field in Man5.fp5 you'll also set the nMan5ID field value in Order.fp5 with the gID field value in Man5.fp5 via the constant-to-constant relationship that exists between Order.fp5 and Man5.fp5. If there is already a corresponding record, you won't create a new record, but you will set the nID field value of the corresponding record in Man5.fp5 into the gID field in Man5.fp5.

You'll create a new record in LineItems and set the nOrderID field value of the newly-created record in LineItems.fp5 with the value in field gID in Order.fp5 and set the nMan5ID field value of the newly-created record in LineItems.fp5 with the value in field gID in Man5.fp5. Both those values (nOrderID and nMan5ID) are set via their respective constant-to-constant relationships.

You then enter the relevant information via the portal in Order.fp5.

Is that enough to get the juices flowing? Again, why do you need a seperate file for Manufacturing Orders?

[Ugo DI LUCA]

Josh,

Not sure at what point you are really, and if my latest thoughts were correct.

If what you need is just a "how to build a Line Item", you may download the FileMaker templates at FMI's Web.

As a matter of fact, I've added an attachment to a post this morning, that may also give you some ideas. Only the price is looked up in it, but you could use the same structure to lookup description fields from the Product File as well.

Variable Prices lookup

[jjosh12]

Ugo!

good to hear from you.

Each Item ordered is a blood type or a serum sample. Each Item belongs to a Lot# (batch#). There can be different batches of the same item and more than one size for each item, but each Batch can only belong to one item that varies in sizes. From filling out an order form (invoice)with a unique invoice number. I want to send the detailed field information of each item ordered to a manufacturing order request form that creates a uique manufacture order request for each item in that order form with that same invoice number.

I hope I didnt just make this more confusing while trying to explain.

Josh

[jjosh12]

John Caballero,

I dont know for sure that I need these files seperate. I thought I would have to have them seperate in order to generate an individual manufacturing order for each item. I have created a generic line item file before but am still getting compfortable with using line items, and I thought this was a possible route to do this. If you have an easier way I would love to hear it.

Josh

[jjosh12]

Ugo,

yes your thoughts were correct:

each item ordered has some parts associated and I want to run

- a Production order with items and related parts.

[Ugo DI LUCA]

Josh,

A "Manufacturing" process usually involve a formula with an "Item to produce" and the "components" to add. There can be different quantities of components to be involved.

Is it what you're doing ?

So that when someone order Product A, B, and C, you want to have a Manufacturing Order Form listing all 3 Items Ordered and say, sorted, their 30 related components, on each row of a Manufacturing form ?

Is that it ?

[Ugo DI LUCA]

Ok,

So it would be a bit tedious...

And there are different options offered to you.

Do you need a report you can scratch when done or do you want these Manufacturing Orders to be stored as the Invoices are ?

[jjosh12]

Ugo,

you are correct everywhere but:

So that when someone order Product A, B, and C, you want to have a Manufacturing Order Form listing all 3 Items Ordered and say, sorted, their 30 related components, on each row of a Manufacturing form ?

I need it:

So that when someone orders Product A, B, and C, I want to have a Manufacturing Order Form listing one of the Items Ordered creating a unique Manufacturing order for each item and say, sorted, their 30 related components, on each row of a Manufacturing form ?

[jjosh12]

Ugo wrotte

So it would be a bit tedious...

And there are different options offered to you.

Do you need a report you can scratch when done or do you want these Manufacturing Orders to be stored as the Invoices are ?

reply form Josh:

I need both the manufacturing orders and invoices to be stored.

[Ugo DI LUCA]

Why would I like all solutions to be complex

No, this one could be rather easy at the end, if each item becomes an unique order.

You'd still need an additional Line Item, say "ManufacturingLI".

Then, IMO, it would be all scripted from within the Order file, but could be modified as wish afterwards.

What the script would do is create a new Manufacture# with the "Product_ID" you've just entered in the Order Line Item (just using the g_ID John was referring to).

Then, from that stored g_ID, your script will loop through the "Component File" and extract, one by one, each related component for g_ID using the ValueListItems functions.

Each Component will then be inserted into the ManufactureLI, with the g_ID becoming the Foreign Key.

A lookup would bring any related data from t_Component_ID into the ManufactureLI.

Tell if you need help for such a script.

[jjosh12]

Ugo,

If you are offering then I will gladly accept help with the script. I don't know exactly what scripting steps to use to achieve this.

Thank You UgoLDEN

thank you

thank you

I am understanding everything you guys have explained. I just havnt got enough experience to find my way to a solution and not forget steps and have no idea where to look for the mistake(s) (or spot the mistakes) but I surely am learning alot from you guys and I really appreciate everyones kindness in helping me out.

Josh

[Ugo DI LUCA]

Josh,

Not sure what you're LDEN means. Hopefully, you forgot one letter someplace....

So see attached something that may help. Better than a thousand words I think.

ManyToManyPortals.zip

[jjosh12]

It is for U-Golden (UgoLDEN) as in your a golden god of Filemaker.

Josh

[Ugo DI LUCA]

You know what.

I've just reached 2,500 posts and Carpal/Tunnel (thought this was down BTW ?), so let God stay in his place...and let' hope I find some lights way through this new tunnel

[Vaughan]

"I've just reached 2,500 posts..."

Well done!

[DanBrill]

And I thought breaking 100 this week was something! And I've been registered 1 day longer than Ugo. I guess its 'cause I don't have a race car.

Thanks to you, Ugo, for all of the great help you give to us all.

Dan

[Vaughan]

Do you have a proper job, Dan?

[dbruggmann]

Congratulation, Ugo!

Ugo DI LUCA said:

...and let' hope I find some lights way through this new tunnel

So let's all light a candle for him

[DanBrill]

Hey, I didn't post 100 times this week! My 100th post - in over a year's time - happened this week.

But a proper job? I'm not sure if any of them are proper, but I'm up to 2.5 of them. That's something I'll try to fix in the next year.

[-Queue-]

Ugo DI LUCA said:

I've just reached 2,500 posts...

<Sigh> If only I had no life, I might be able to catch up one day.

Kongratz, Yu-gi-oh

[Ugo DI LUCA]

Vaughan said:

Do you have a proper job, Dan?

There's surely some truth here, even if 90% of my posts are made from 20:00 to 03:00am.

You'd better ask what kind of life I have then, which I'm also wondering...

But this will (I hope) probably change when I'll have sold my tile wholesaling company and start a new life as a developer on a 100% daily basis.

A project which got delayed but is still in my plans, waiting for better times to jump in...

Since I've redged these Forums, I felt so impressed by its efficiency that it became natural to give back some contribution, once I had my own job problems solved.

Glad I could help some of you, but I have so much more to learn from you all...