A line item for each module

[Ugo DI LUCA]

In my business, a customer order can be delivered in 2 or 3 times, so invoiced in 2 or 3 times.

This isn't the unique problem, but this is the start point of the new solution I'm working on.

I actually use a unique line item with all my operative modules (quotes, cust order, purchase orders, shipping, invoice,...) relating to it via their respective numbers.

As I want my new solution to use a real relational structure, I came with a solution where

- each module file use its own Line Item file,

- each Line Item file relate to a Main Line Item with a Unique Id

That mean I can now easily use the portals in each file either :

- to create new related records in the parent file.

(for exemple, a button at the end of the portal set a script that create a new record in the Main Line Items and set the relation with the Quote Line Items).

- to use previous record from quotes, cust order,...

(I use a global to list every quotes for the same client, then a portal from quotes let me select the product I want to "export" to the customer order, using its Unique ID Key).

- My Main Line Item File shows every record in different portals, I can edit, modify any records from it.

This can even allow me to see the process/history of the negociations, keeping the records from my first quote (even if the unit price changed in the cust order)

As an example, a line item N

[Ugo DI LUCA]

Coucou!!!

Hey!!! Anybody there ?

I hope I shouldn't be so afraid....

This is really a central point for the structure I intend to build so if any of you have a comment, please do make a comment this post.

[spb]

I think if you set up your relationships and line item ID carefully, then you would only need one line item file, the main line item.

I have a line item file where the line item ID is created by taking the purchase order number and attaching the line number, in a calculation field. A new P.O. numbers the lines from one to forty (or whatever) automatically at the time of creation. So P.O. 02011822 wil have lines numbered 01 and 02, and the line items will have generated the ID numbers of 0102011822 and 0202011822. This keeps the line items unique, and allows for easy relating, portal viewing & etc. For your multiple line item situation, I would just letter each type of line item, A through M or so, then attach this letter, via script, to the above number, so there would be a difference between A0202011822 and B0202011822. This has one advantage of allowing you to visually decode the IDs at a glance. You'd know that a record is line #2 of P.O. # 02011822 in module B.

I'm not sure I grasp your solution well enough to offer much constructive criticism, but I can recommend another book by Moyers & Brown, Advanced FileMake Pro 5.5 Techniques for Developers. This goes into structural theory quite a bit, discussing asolutions for creating multi-related purchase-order based systems. On Amazon, search for ISBN1556228597.

Steve Brown

[Ugo DI LUCA]

Not sure I understood your solution. I tried drawing your keys in an excel file but it didn't helped for what I'm looking for.

More clearly, please suppose :

- Steve is a professional account billed at month-end on shippings/deliveries

- A quote 02011822 is made for Steve with 50 units of Product A at 5,00$ and 100 units of Product B at 10,00 $

Line shows Q0102011822 and Q02102011822

- A customer order 03044935 is made, after negociations, for 70 units of Product A at 4,90 $ and 120 units of Product B at 9,00 $

Line shows C0103044935 and C0203044935

- Steve receives these products in 3 shipments :

Shipment N

[Ugo DI LUCA]

As every answer in this forum is rich and usefull, at a second thought, what do you think of having :

a Central Line Item with autoentered key A02011A822E

a Quote N

[spb]

I think your most recent smaller post, above, is getting closer. Essentially, when the first contact with a customer (the quote) is made, an order number is generated that populates the line item file. This order number is stored in its own field, not the unique ID field. The script that does this, takes this same number & populates the unique ID field with a quote modifier.

Later, the quote is approved with changes. The user activates a script that finds the line items by order number and duplicates the set. The user goes through them to modify quantities, kill some items, maybe add others. When editing is done, the "save" script fills the unique ID field of these line items with Order modifier plus order number.

The shipping department can run a script that pulls up the line items by order number, with order modifier attached to find criteria via script. The agreed-on set is made into an invoice with a similar process, duplicate the order-modified set of line items, and edit according to what's available for shipping to produce an invoice of goods actually shipped. The save script populates the duplicated set of order-modified line items with the original order number plus an invoiced modifier. If you need a separate invoice number, it can be generated into a separate field.

A month later, a script can call up unfilled line items (find order-modified group, omit invoice-modified sub-group), and a new editing/invoice creation process can happen.

All line items can stay in a single database, and all info is retreivable from a single source, through careful finding & sorting. You could find just the order number by searching that field, and get the whole history in one group, original quote, agreed order quantity, multiple shippings.

In general, you need a header database, that stores one record for each order number generated, the line item db we've been discussing, and a product database.

By keeping all line items in one database, you aren't confining yourself to an arbitrary set of modules. A worst-case scenario might be a quote for 10,000 units of product A that goes through several stages of negotiation, requoting and further negotiation. The agreed-on amount is then shipped on twenty invoices of 500 units each, spaced over a year. Using the above database outline, you could have an indefinite number of "versions" of the same line item on the same order number all in one database, and retrieve all or part of this history at will.

I went through a similar process setting up a purchase order/receiving module. To aid myself in visualizing the flow, I made a set of very simple test databases, header, product & line item, with the appropriate scripts that create purchase orders. The header database is the "home" database, where the scripts are triggered. These scripts use constant relationships (or "degenerate" relationships someone here called them) and global storage fields to pass data like customer ID, order number & etc. back and forth to each other.

If you think it would be useful, contact me privately and I'll email these test databases to you, so you can pick apart the scripts and relationships.

Steve Brown

[Ugo DI LUCA]

This is the clearer long technical post I ever read. I must say I'm impressed by the technique you suggest.

Reading along your post, here is what I thought. Oh my god, this guy is doing exactly what I was doing years ago without a computer

At that time, I had paper files in individual archives named Quotes, Customer Orders, Purchase Orders, Shippings and Invoices.

- Quotes, CustOrders, Shipping and Invoices were classified by alphabetical name of the customers in a big "armory" with big "harmony"

- Purchase Orders were classified by alphabetical name of the Manufacturer/Supplier.

When the customer approved the quotes, I created a new Customer Order File, moving into it the precedent quotes file for this customer. If not the whole quote had been ordered, I kept the quotes file for a while in the main Archive...

At the end, I could have a 25 pages dossier for each invoice, and classified it into the Client Archive.

But nobody ever figured out how it would be possible to see at a glance in the PurchaseOrder Archive where was the PurchaseOrder related to the Customer Order 1006 for Mr JOHNSON, when this silly guys asked at the phone, on Friday at 6:00 pm, when his ordered could be shipped.

I think that is why I basically changed from pencils and Archive to Computers and disks.

But...

I am sure I'm not the only one that sometimes forget that the basic work procedure (if efficient) wouln't be changed because of the introduction of computers and databases.

Basically, you just reminds me that FM would make my "worklife" easier (it is just a better way to classify, view, edit and store relational records) but wouldn't change at all the work itself...

Thank you for that Steve, and receive all my thanks for your clever post and logic.

Rendez-vous at the Private Message Board for your test file. I no longer could wait to ask for your test file to see how all this could work...

[Ugo DI LUCA]

This is an old post, but as the method suggested did really work perfectly, here is a copy of a message sent to Steve.

Ok, I didn't have so much time, but I already can say that you are right saying that I can easily work with one line item file only.

Using your file, your posts, and other suggestions posted on the forum, I made a test flat file (in FM this time) to see the process with a simulation into 35 lines in the Line Item file, on a line per line basis (one line for quote, another for customer order, ... using the standard Quote N