Purchase order design

[spb]

I'm a self-taught FM developer. As such, there are some aspects for which I have a fairly sophisticated knowledgef by now, but other very basic aspects which elude me. I have a company running on an old DOS inventory system. I am gradually replacing its various modules with FM versions, working with daily ascii imports.

I am now designing a purchase order module (Mac, FM Dev 5.5). I'm pretty confident about scripting, calculations, the need for concatenated ID fields & etc. But the overall design aspect escapes me. This is ther best I've come up with:

I have three dbs (FM versions of the DOS system's dumps)

Item file

PO Headers file

PO Line Items file

To create a new PO, I have set it up so a PO worksheet is created in the Item file. When our buyer makes her selections, by filling in an order quantity field on the worksheet layout, then she triggers a script that does these things:

Makes a found set of just the ordered items (qty >0)

Opens PO Headers file & runs external script that:

finds the current highest PO number, adds one & stores result in a global. Then the script copies this new number and:

Opens Item file (where found set remains) and runs external script that:

pastes empty PO field in all records of found set with new PO number (loop?), which should be on clipboard. Then it opens PO Line Items and runs external script that:

Imports select fields from found set in Items, creating the PO Line Items records. It does some other stuff to fill PO Line Items fields not present in Item records. Then it has a script step of "Go to record/request First", where it copies the contents of unique ID field, then runs a find on this field. This will leave a found set of one. Then it opens the PO Headers file and runs an external script that:

Imports select fields from the single record in the PO Line Items file found set, creating a single Header record. I envision the PO number that has been passed around these dbs, the vendor # and the date as all I need here. Then it Goes To Related Records in the PO Line Items file and runs an external script that:

goes to the printed PO layout, sorts and prints.

For clean-up, it should then open the Items file and run another external script that:

runs a Find on the Order Quantity field for >0, then Replaces this field in these records to zero. Also Replace the PO Number field for these records to zero.

I could create all this, and I'm pretty sure it would work. But it seems clumsy and Rube Goldbergish to me. This is a design situation that has been solved many times, so I'm probably overlooking something basic. Any ideas?

Steve Brown

[LiveOak]

Seems like a lot of stuff. How about just a P.O. file and a P.O. Items file? You can create new P.O. items in a portal from P.O. or enter into global fields and click "add" and have a script add the item to the P.O. Items file. Either way, the portal display of the P.O. line items is ok. Your "draft" purchase order can still be in the P.O. file. Connect the line items with something other than the P.O. Number (record id, etc.). When a P.O. is made final, you can assign the number to it.

-bd

[spb]

>>How about just a P.O. file and a P.O. Items file?

My three files already exist & are fairly complex. The Items file has 16,000 records and is used for other purposes than PO's. The Items file stores all items from a vendor, plus a ton of necessary sales history info and the like. Typically, a vendor will have 300 products listed in Items, from which we'd create a PO for maybe 20. Sometimes the PO's can get up to 150 lines, so I wanted to avoid printing a portal if possible. If I were to use just the PO line items file, would I be storing info for all the items, the 280 *not* being ordered? As records with no assigned PO? The line items file is existing, and is a version of that in use by the old DOS db.

>>You can create new P.O. items in a portal from P.O.

I wondered about this. The checkbox in Portal Setup controls the function of deleting related records, but there's no equivalent check to allow the creation of related records.

>> or enter into global fields and click "add" and have a script add the

>> item to the P.O. Items file.

This is interesting, but I was trying to avoid having the buyer do an extra step for each line. It seems like Finding the items with positive order quantities entered would do the same thing. The concept of "adding" them to the PO Items file is where I got hung up and came up with my very clumsy workaround.

>> Either way, the portal display of the P.O. line items is ok. Your "draft"

>> purchase order can still be in the P.O. file.

Hmm. Maybe. But printing it would return me to the problem of printing a huge portal that stretches over multiple pages. I've been down that road before and am not keen on returning!

>> Connect the line items with something other than the P.O. Number

>> (record id, etc.). When a P.O. is made final, you can assign the number to it.

This is quite interesting & I will have to think about it. Currently the unique ID for a line item is a concatenation field of the PO number & line number. This, again, is an inherited structure from the DOS source (I created the calc field that puts these together). So I thought I'd have to assign the PO number before making the line items, to ensure uniqueness of ID (auto-assigning line numbers is part of my script I didn't bother to mention).

Unless I can figure out how to get the base item info into the PO line items file (which contains three years of line item history at the moment, 33,000 records, and any individual item could be represented by a dozen old orders/PO numbers), then I pretty much have to do the initial listing of available items plus sales history from with the main items file. The problem then becomes how to easily generate new line items in the PO items file, plus a header record, all with the new PO number?

Steve Brown

[LiveOak]

A couple of things. First, I think we have a nomenclature problem. A conventional P.O. Line Item file stores the items on some purchase order, not a list of all possible items. I would consider your items file a "Product" file to avoid too many things called items. If your P.O. has three items on it, the P.O.Items file will have three records for this P.O.

Next, you never print from a portal, print the P.O. from the P.O. Line Items file. All the customer informaiton is displayed as related fields from the P.O. file. The "allow creation of related records" is a characteristic of the relationship, not the portal. This checkbox does indeed exist, in the Define Relationship dialog.

The P.O.Items file never really "contains" the Product information. All that is needed is the product number. All product data needed on the P.O. can be displayed with related fields from the Product file.

The P.O. line items can be created by entering the product number in a portal or selecting the product number from a pick list and having a script create the new record in P.O Line Items.

-bd

[spb]

>> I think we have a nomenclature problem. A conventional P.O. Line Item file

>> stores the items on some purchase order, not a list of all possible items.

>> I would consider your items file a "Product" file to avoid too many things

>> called items. If your P.O. has three items on it, the P.O.Items file will have

>> three records for this P.O.

As this is exactly what I thought I was describing, then you are right. A nomenclature problem it is. Sorry about being unclear. Wherever I referred to an "Items" file above, read "Products". When you said "How about just a PO file and a PO Items file?"

I thought you were saying to somehow include the entire Product file in the Items file, giving me two files to work with, not three.

>> Next, you never print from a portal, print the P.O. from the P.O. Line Items file.

I've already learned this the hard way.

>> The "allow creation of related records" is a characteristic of the relationship,

>> not the portal. This checkbox does indeed exist, in the Define Relationship dialog.

Ah! I knew there was something fundamental and obvious I was overlooking!

>> The P.O. line items can be created by entering the product number in a portal

>> or selecting the product number from a pick list and having a script create the

>> new record in P.O Line Items.

This is the precise sticking point of my original post. I will play around with a relationship that allows creation of related records. I think I can set it up so all the line items are created in the line items file this way. But I would also need to create a header record in the header file (what I think you are referring to as the "PO file"), and apply the new PO number to all the line items and the new header. Doing this job is how I came up with the overly-elaborate workaround in my original post above. My instinct tells me there must be an easier way to do this, hence I'm posting it in "Relational Database Theory", hunting for some basic theoretical advice.

Steve Brown

[LiveOak]

In the approach I am suggesting, the entire P.O. is created from within what you are calling the header file. The reason I call it the P.O. file is that a record in this file is a Purchase Order. The portal showing the items on the P.O. resides in this file. The order of operations is to create a new record in this file (a new P.O.) and then add line items to it.

-bd

[spb]

>> In the approach I am suggesting, the entire P.O. is created from within what

>> you are calling the header file. The reason I call it the P.O. file is that a record

>> in this file is a Purchase Order.

I guess it's a matter of nomenclature again. The PO (header) file is small. There is one record for each PO, and the fields involved consist of PO number, Vendor number and a few unimportant extras (date, terms, etc.). The term "header file" is one used by the old DOS database I am trying to slowly convert into FM.

>> The portal showing the items on the P.O. resides in this file. The order of

>> operations is to create a new record in this file (a new P.O.) and then add

>> line items to it.

I just made three small test dbs, and learned the value of allowing creation of records in relationship definition. I made myself a portal in the PO file, and it works nicely. There are some fields in a line item record that need to be filled in, some can be done with a relationship between Line Items and Products, others can be filled if I place a button next to the order quantity field in the portal in PO.

But there remains a basic sticking point. The buyer needs to enter the product ID number in each portal row, along with her quantity. I have created a PO worksheet layout in Products that will list all products by a vendor, with elaborate sales histories for the buyer to review. Ideally, I would like her to fill in order quantities directly on this list and click a button that would make line items out of everything she entered a positive quantity for. But how to get the "header" record created in the PO file, and the PO number applied to the new line items is my headache.

I could ask her to type in the product ID numbers along with her order quantity in the PO portal, and this would work nicely. But our ID numbers are ten digits long, and she often builds POs 150 lines long selected from 2,000 products by that vendor. This makes another portal in the PO file, in which products associated with that vendor are displayed somewhat impractical. She needs to print the full list to study, so I set it up for her in the Products file.

But even if a Product portal existed side by side with the PO line items portal, how to select a group of products in one portal, and have them automatically become line items in the other?

This is, I think, a restatement (one that is, hopefully, more clear) of my original post. The workaround I came up with is the series of imports between Products, Line Items and PO to arrive at a set of line items selected in Products, each with a new PO number found in the PO file, and with a new PO record containing this number.

Steve Brown

[LiveOak]

You can check off items in the Product file and have a P.O. created and the items added by scripts. Some checks will be needed. If the buyer just checks off a bunch of stuff in the product file AND the products are from two different vendors, should two or more P.O.s be created?

Import is ok, but I rather see you add the line items to the P.O. using a looping script. If multiple P.O.'s are to be created, this is pretty much necessary. In this case the procedure would be to sort the marked items by vendor and create a new P.O. every time the vendor for the items changes.

Also, be careful how you mark items. If this database is multiuser and you have two people entering into a "marked" field to mark products, then perform a find to isolate the set to be added to a P.O., the two users will interfere with each other (the found set will contain BOTH user's items).

Instead, mark items by adding the item numbers to a global field. Display the checkmarks by using a calculated field which checks PatternCount of it's item number in the global field. This way two users can mark records and not interfere with each other.

The procedure might look kind of like:

find marked items

sort by vendor

go to first item

loop

P.O. on P.O. list? No, create new P.O. Add to P.O. list.

Add item to P.O.

go to next item

exit after last

end loop

-bd

[spb]

Ahh! I think I can see some light at the end of the conceptual tunnel. I believe my needs are somewhat simpler than you had envisioned for two reasons.

>> If the buyer just checks off a bunch of stuff in the product file AND the

>> products are from two different vendors, should two or more P.O.s be created?

No. Only one P.O. at a time will be worked on. Selection from products will be made from those by a single vendor. Also, for now anyway, there will be only one user of the solution, so there won't be any need to check against simultaneous P.O. creation, thus (if I read it correctly) no need for the global field.

>> The procedure might look kind of like:

>> find marked items

>> sort by vendor

>> go to first item

>> loop

>> P.O. on P.O. list? No, create new P.O. Add to P.O. list.

I don't quite get what you mean by "P.O. list". If this script is running in the P.O. file, it would probably create the P.O. first and assign the next highest number. Then start adding marked items to it. The way our buyer works, it's pretty much guaranteed that all marked items are from one vendor, and that a brand new P.O. for this vendor is needed.

>> Add item to P.O.

Here's where I bog down again. I believe that adding an item to the P.O. means create a new line item record for this product, and set the P.O. field in this record to the freshly created new P.O. number. Makes sense. However, how does one make the record in the line item db from the P.O. db? The "New Record/Request" script step makes a new record in the P.O. db, not line item. I know I'm missing something simple and basic here. Duck into the line item db with an external script & make a record? Then we have the problem of copying and pasting the new P.O. number into the line item P.O. field to keep it related to the P.O. record. We also have to duck back to the P.O. file to pick up our looping script again, right?

>> go to next item

>> exit after last

>> end loop

Steve Brown

[LiveOak]

The concept of a P.O. List was to keep track of the P.O.'s created in a multi-vendor situation. You will still need to check for the selection of products from more than one vendor, even if only as an error.

The fundamental concept missing is that the Perform Scrip() script step has an "External" option which allows the selection and execution of a script in another file. To create a new record in P.O. Line Items, run an external script in the P.O. Line Items file. To pass data, such as P.O. Number (I don't recommend linking by P.O. Number, or any other number the user recoginzes, use a record ID instead) create a "constant" relationship to allow the accessing of global fields in one file from another file.

The constant relationship is create by defining calculated fields in all files:

kOne (calculation, number, indexed) = 1

and creating relationships between files matching these field. For accessing records this is a degenerate relationship, as all records match all records. But, for accessing global fields in File B from File A, it's just the ticket. This allows you to set a global to a key in File A, jump to a script in File B, create a new record, and set the key to the File A global. Pretty useful for passing data from file to file, much safer than cut and paste.

The way scripts work:

***Script in File A***

SetField()

Go to field

bla

bla

Perform Script(External, File <--- call to script in File B

Go to Layout <--- executed at completion of script in File B (assuming no Halt statement in File B script)

When the script in File B completes, File A will be brought to the foreground and the Go to Layout step (in File A) will be executed. If there is no next script step following the Perform Script(External), you will be left with File B in the foreground.

-bd

[spb]

Thank you!

Running an external as a subscript looks like the very basic understanding I was missing. I vaguely thought that if you ran an external, you couldn't come back to the parent db without turning around (in the child) and setting off *another* external. I did some testing and it works like a dream.

Also, the tip about using a constant relationship & globals is pure gold. I was worried about cutting and pasting between databases, it seems clumsy and fragile. This is much better! Now I can spend the next few days doing the donkey work, but the tough job is finished.

I'm trying to repay you (for this and when you pulled me out of the fire last week) the only way I know how. I'm actively perusing the forum, hunting for newbies even greener than I to answer the questions they have which you've probably answered 100 times by now.

gratefully,

Steve Brown

[LiveOak]

Thanks, Steve. Participation is our goal. No one knows it all, but everyone can contribute by helping someone. This spirit is one of the great things about the FileMaker Community.

-bd (Brent Dussia, Live Oak Systems)

[spb]

I have created three smal test files (PO, items & products) with sample data that incorporate all relationships, fields and scripts that perform the task discussed in this thread. I made these for myself, so I can carefully apply the techniques to my "real" databases, but these might be useful to others. If anyone is reading this who would like copies, send me a note and I'll email them to you.

Steve Brown