Duplicating Order with LineItems

[PiedPiper]

Hi forum! My structure is (I think) the basic structure: Orders file with OrderID joined to a LinItems file on OrderID. LineItems are created by clicking into a portal in Orders.

What I want:

If Sales clicks a button called 'duplicate' on an order form, it duplicates the order and all of it's lineitems. I want to control what is duplicated (I don't want to duplicate the Order ID for instance, or the Creation Date or Order Date). The order date is currently set Auto Enter calculation of Status(CurrentDate).

I started by creating several global fields to hold the data I wanted to duplicate and then scripted using:

Set Field [globalSalesID, SalesID]

Set Field [globalCustomerID, CustomerID]

And then reversing after I create the new record (set CustomerID, globalCustomerID). Is there an easier way? Just in starting, I have 8 globals holding data. Yikes! And the scripts seem too complex. I don't want to use the regular Duplicate command.

And I get totally lost on duplicating an entire 'related' set of lineitems in the lineitems file.

I think I'm making it too complex. Can someone please just give me some ideas on how best to do this? I would sure appreciate the help.

Pete

[Fenton]

As you've discovered, it's often easier to duplicate the whole record, then reset a few fields to new values, than it is to tediously duplicate each field using globals.

Of course, the fewer "redundant" fields, the more relational the better.

The main "gotcha" in duplicating the several line items (after using the relationship to isolate them) is that the position of the duplicated record is "the last record" when the records are unsorted, but "the next record" when they are sorted.

You can do it either way, but you must take the position of the duplicated record, and its original, into account within your Loop.

I often use a Loop I learned from Eric 'the genius' Schied, a Sorted Duplication using 2 Omits:

Enter Browse Mode []

Freeze Window

Go to Layout [

[Fenton]

BTW, in my previous post, the Sort by SerialID is a kind of "fake" sort, since it returns the same as creation order. But I needed a sort for the Loop.

Here is S

[PiedPiper]

Hi Fenton, thank you for the idea. I'm not sure I understand how this will work. I want to duplicate the current order (or the order being viewed when Sales clicks 'Duplicate.') I even considered using Omit and then Show Omitted to isolate it and using a Global text with multiple reps to save adding so many globals. And it seems that if I'm on the order I want to duplicate, I could somehow use Go To Related Show to give me the LineItems I need to duplicate. All I would need to change is their OrderID, I think. Your using a loop and omit makes sense for this part. But I won't know the OrderID to insert into the lineitems until I create the new order and it assigns the OrderID. At that point, I'm no longer on the original Order anymore, so I guess I should capture the original OrderId in a global first. It's just hard to visualize how this needs to happen.

So once I've capture the Original OrderID, I can go to related show - which will give me only the lineitems relating to the original order then script something like?

Go to First Record

Loop

Duplicate Record

Set OrderID, Orders::globalOrderID

Omit

Go To First Record

Omit

End Loop

Is that making more sense to me??!!

So, even though I don't understand, I'll try it. Just because I don't understand something doesn't mean it won't work. I'll let you know what I figure out. But I guess on duplicating the order, I'm stuck setting each field, right? If I could allow it to duplicate, might it be easier to then change the OrderID, Creation Date, etc?

Thank you for answering me.

Peter

[Jim McKee]

Peter ...

Check out this thread. I think it might address some or most of what your're trying to accomplish. Also, be sure to read this post.

Good luck!

[PiedPiper]

Hi Fenton and Jim,

Thanks so much for the help. And thanks CHUCK!!! I ran into a bit of trouble. My mistake was using a global in the Orders file to hold the new Order ID. And, since I didn't want to duplicate the order but instead create a new order (so OrderID would auto-enter, etc.), I had trouble accounting for it - I'm still pretty new at this.

But the following worked wonderfully, so I thought I'd share it. Also, you may see problems with it that I can't see ... and you may have further ways I can improve up on it.

Orders script (Duplicate Order)

# "Capture all fields from the Order that you want to duplicate."

Set Field [ gSalesID, SalesID ]

Set Field [ gCustomerID, CustomerID ]

Set Field [ gShipVia, ShipVia ]

Set Field [gTerms, Terms ]

# "Isolate this Order's line items."

Go to Related Record [ LineItems ]

[ Show only related records ]

New Record/Request

Set Field [ LineItems::gOrderID, OrderID ]

Set Field [ SalesID, gSalesID ]

Set Field [ CustomerID, gCustomerID ]

Set Field [ ShipVia, gShipVia ]

Set Field [Terms, gTerms ]

Perform Script [ Filename: "LineItems.fp5", "Duplicate LineItems" ]

[ Sub-scripts ]

Refresh Window

[ Bring to front ]

Lineitems script (Duplicate LineItems)

Unsort

Go to Record/Request/Page

[ First ]

Loop

Duplicate Record/Request

Set Field [ OrderID, gOrderID ]

Omit Record

Exit Loop If [ Status(CurrentFoundCount) = 0 ]

Go to Record/Request/Page

[ First ]

Omit Record

End Loop

Show All Records

I loved learning to use omit in this way. The only thing I'm not crazy about is duplicating the lineitems. I had a unique LineItemID that get's duplicated. But I got lazy and didn't want to write all the globals to set each field like I did in Orders. Is it necessary to have a unique ID in lineitems? I did it just because i have a unique ID in every db. Any additional input would really be appreciated.

Pete

[bruceR]

In the items file, if you have a relation from gOrderID to OrderID, you could change the Show All Records statement to Go to Related Records OrderItemsByGlobal, and thus show only your new item records.

[PiedPiper]

Hi Bruce,

I removed the Show All Records and created a relationship from LineItems to Orders joining gOrderID to Orders::OrderID and then added Go To Related Show. I see no difference, so I guess that's good! However, it leaves my LineItems file with 0 found (although it did create the new lineitems).

Is there a problem with leaving a file with no records being displayed? The lineitems still show in the orders portal even though they don't show any more in lineitems. Could this ever create a problem? I thought I should return the file to all records being shown. Thanks for helping me.

P

[Fenton]

An auto-entered incrementing SerialID will NOT be duplicated with that command. It will get its new unique value. FileMaker ain't that dumb. You should check that your Line Items serial ID field is set up correctly.

[PiedPiper]

Hi Fenton,

My unique OrderID is based upon auto-enter by calculation. I use gDBid (global, text) and gDBvers (global, text). Then I have Serial (text) with auto-enter serial number beginning with 00001 increment by 1, index off, turn on if needed. Then I have my OrderID (text) as auto-enter by calculation with: gDBid & gDBvers & Right("00000" & Serial, 5), Do not eval, index off (index if needed). The serial doesn't duplicate but unfortunately, the OrderID does.

Have I done something wrong that it duplicates the ID? Is there a way to stop it from duplicating it? I considered using my OrderID calculation in the script to reset the OrderID - then I'd be able to duplicate it, wouldn't I? I guess I'm just concerned about messing with my unique IDs. I know that the Creation Date inserts the new date (doesn't just duplicate), but I can't stop the ID from duplicating.

What do you think? I appreciate your advice on it.

Pete

[Ugo DI LUCA]

Just some ideas about the duplicate order in a Business Solution...

For customers currently having regular identical orders, you'd better use another CustomerItems file.

Then, raher than duplicating an order, the user mark in a portal in the Customer file those items (or all) that needs to be ordered, and script a "New Order with Marked Item". You can specify wether to set this order with Default quantity or without.

The CustomerItems file also keep track of all special prizes that this customer may have.

I don't like much a "duplicate" button on the layout.

If you were duplicating items for a "complement" order and are using the same line item for orders and invoices, then you'd better create a second unique_ID and group all items with this new ID. If it happens one order may be splitted on 2 separate invoices, you'd get into trouble filtering those orders afterwards.

This doesn't seem to be your case though. Never know.

[PiedPiper]

Hi Ugo,

You said, "Then, raher than duplicating an order, the user mark in a portal in the Customer file those items (or all) that needs to be ordered, and script a "New Order with Marked Item"."

I'm unsure on how that is different than creating a new order?! Many times a customer will say to order exactly like they did before. There are specific requirements for certain customers - such as having sku's, their stock numbers, special shipping requirements (for only certain stock shipments - not all shipments) or other such specifics. If everything is identical to another order, it just seems natural to duplicate it.

I've decided to use the duplicate record feature. After the record is duplicated, the script will use the 'new serial' generated and update the OrderID. I tried it and it works great and is much easier than creating globals and setting all the fields. The script also will change the Order date to today, etc.

You DID hit upon something else ... Inventory contains WholesalePrice and RetailPrice (we sell to both types of customers) for each product. The customer file has an option to specify that this customer gets Wholesale, Retail or a certain discount (off wholesale or retail) but we have some customers who get their OWN special prices. Instead of adding FIELDS to the inventory item, I'm considering splitting the prices from the stock as a related file. In this way, special pricing can be added easily (and referenced to the CustomerID) or globally to all customers (sales, etc.).

My problem with this idea is how to link them. If every customer is flagged on what TYPE of price to use (Retail, Wholesale, %of either, special price or sale price)and my priuce file has a Type field, how would the price file be related to an order (through the Inventory file)? I can't picture how this would join together. I don't want to make these changes just to find out it won't work. Thanks for the ideas.

P

[Ugo DI LUCA]

Hi,

The Preferencies for any special customer would be stored in a CustomerItem.fp5 file, not in the Customer File.

Special pricing should be stored there also.

Then, you'd retrieve any special prices easily without storing them in the Inventory file. They are assigned to a customer, for a given set of items. So you can't save them in the Inventory (Product File) nor in the Customer File, but you can store them in an InventoryForCustomer file (my CustomerItems.fp5).

They still would be accessible by the Product_ID and the Customer_ID or a concanation of both keys.

These special prizes had driven me mad for a long time, before I started using this external file.

[Ugo DI LUCA]

Hi,

Sorry the later didn't answer your last question.

PiedPiper said:

My problem with this idea is how to link them. If every customer is flagged on what TYPE of price to use (Retail, Wholesale, %of either, special price or sale price)and my priuce file has a Type field, how would the price file be related to an order (through the Inventory file)? I can't picture how this would join together. I don't want to make these changes just to find out it won't work.

Keeping the idea of your external price file then.

Assuming you don't want it to be a related calculation, which I'd agree, you could have your price field be a lookup of a calculation, which could be :

c_PriceToLookup = Case(IsValid(R1::SpecialPrice),R1::SpecialPrice, R2::PriceField)

Where R1 is RelationshipToPriceFile and R2 is RelationshipToInvetory.

[bruceR]

I removed the Show All Records and created a relationship from LineItems to Orders joining gOrderID to Orders::OrderID and then added Go To Related Show. I see no difference, so I guess that's good! However, it leaves my LineItems file with 0 found (although it did create the new lineitems).

Welllll, that's not what I recommended.

The relationship should be a self-relation in the items file, from gOrderID to OrderID. This will show you your newly created records. Yes, there are problems associated with zero found sets.

[PiedPiper]

Wow, well I've certainly got plenty to work with now. I'll set up the basic structure tomorrow (with the separate CustomerItems file). So it's like a Preferences file then - except each Customer can specify their unique requirements? Currently, I had duplicated our previous order strucuture and needs and now everyone tells me it would be nice if we also had ... well, you know. So I'm trying to think ahead on this design. The ability to change sales and specific client needs would really open things up for us. Thanks Ugo!

Hi Bruce,

Oh. I really try to read everything suggested and thought I had did as you suggested. Now it makes more sense, thanks. I'll channge it and try it tomorrow too.

Thanks guys for helping me through this.

Pete

[Ugo DI LUCA]

Hi,

Here are some other points you'd like to consider, based on some previous experiences with Business App developement.

Whereas FM would have this ability to create related records from a portal in the Order db, there are some parameters, which depending on the business characteristics, won't be correctly adressed using the straightforward method.

A Customer Order could be placed according to these given basic "sequences" (not mimited to these) :

1. Order built upon existing quote

2. Order built based on previous Order :

- Order Complement (not all the goods arrived on time)

- Order Refill (the quantity first ordered wasn't sufficient)

- Regular Order (a basic weekly, monthly set of items)

3. Order built from scratch

4. Order where items are added to 1 or 2

5. Order where items woud be substituted from a previous set

6. Orders with special prices.

Even scripting creation of related records from the Order file would be tedious.

This means that although Lookups and related calculations may help, you'd better create this order where you'd have a good visibility of what the History for this customer might be.

Thus in the Customer File IMO.

You need to adapt FM to your needs and not the contrary, and even if it doesn't seem as dynamic, scripting method would help keeping away from unstored calcs, which lessen the whole db speed.

Also, going backwards to your original querry, what would happen if you needed to duplicate an order less some items. Would you let the user delete that not used line ?

A layout in the Customer File, with :

1. a "view only" portal linked to the line item to offer a wide view of :

- its quotes not yet ordered

- its items ordered and not yet delivered

2. a portal to the preferencies file with all selected items with special

3. Another one to this file with the "Default" order set (using a MultilineKey from previous flags)

4. a portal to the latest order (to line item), based on a "stored" MultilineKey, that would be scripted when the order is printed

5. A portal to the Product File, filtered by the global field where you currently select the item to order (in the one after one step)

6. A portal to a Temporary Order Set.

Each item from all portals can be picked by a single SetField script step and moved to the Temporaray Order set.

Once this order is done, script its creation to the Line Items.

May be to many portals and too many "personal" ideas, but you got the point I think.

HTH