Order entry using seperate file for line items

[sbg2]

I am in the process of updating our order system from a single file that has a field for each item that can be ordered to a new system that stores the items in a seperate Line Items file. For display and orginizational purposes I have a script that imports 1 of each particular item into the Line Items database which makes order entry much easier as opposed to selecting from a drop down list.

My concerns with this system are:

1) Might I encounter problems when this system is deployed and more than 1 user tries to create a new order at the same time? Right now when a user clicks the New Order button a script is run in an Items file which finds all items marked for Orders and exports them to a file called "1 of each for US_Orders.tab". This '.tab' file is then imported into the Line Items file which gets populated with all items and sets the ID_Order to the appropriate order. When the user Prints the order any Line Item record with a 0 qty is deleted (helps limit file size).

2) When a user chooses to Edit an Order another export of "1 of each..." is executed and imported back into the Line Items file only adding items that do not exist. This can cause records that have the same ID_Order to become seperated. I'm worried about the effect this will have on performance. I suppose I could set-up a periodic export/re-import of all the line items records (sorted by ID_Order) and re-index them that way.

3) Is there an easier way to get the Single Table feel to an order system while still utilizing a seperate Line Items file?

[Reed]

I don't understand why you're doing all this complicated importing exporting stuff for this...

Couldn't you just have an orders table linked to a line items table where you view and enter line items in a portal? Customers---<Orders---<Line Items>---Products is a pretty standard many-to-many scenario that it used as an example in just about every book on relational databases.

There are probably several example files posted on these forums with just this structure. What do you mean by "Single Table feel"?

[sbg2]

I don't understand why you're doing all this complicated importing exporting stuff for this...

Couldn't you just have an orders table linked to a line items table where you view and enter line items in a portal?

Because its much nicer looking and user friendly to have a form like this:

All possible items are listed and organized, user only needs to enter in the Qty field. When a user Edits an order all items will always be in the same place.

As opposed to this:

Here the user has to choose from a long list of items and then enter the Qty. While items could be sorted its not as user friendly as listing all the items, all the time.

[RalphL]

I have made a sample file of 3 tables: Order, Product & OrderLine. There is 4th unused table in the file. Product is a single record table. In Order I have a portal that displays the products and accepts quantities. The quantity is recorded in the OrderLine table. To make this work I have a global field in Product which holds the current order number. This require that the user must create new orders by the button I have provided and must navigate to different orders by the buttons I have provided.

I haven't tested this in a multi-user environment.

[Reed]

You don't have to use a value list to choose products for your line items. You could use a portal to list all products, and another portal to list the line items for an order. A user could click a button on the products portal to add it to the line items. Once the list of products got large, you could add filters to the portal to limit records shown by category.

If you really want all the products to show up all the time in the line items portal, you could run a script each time an order is created to make a line item for every possible product where the initial quantity is zero or null. The user can just enter a quantity for the items to be ordered.

It seems like the way you're doing it now, you'd have to change the layout every time a new product is added... wouldn't that get to be kind of a pain? And wouldn't the layout get large/unwieldy if you had a large number of products?

[sbg2]

I have made a sample file of 3 tables: Order, Product & OrderLine. There is 4th unused table in the file.

If you scroll through the A_Detail records with the Mouse Wheel the portal is not updated. That would be dangerous, the screen told me I was on Order O100002 but I was actually changing the Qty for order # O100003. Using the Previous, Next, First buttons seemed to work ok.

Another big problem is if an items unit price changes (which it will from time to time) all orders, regardless of what the pricing was when the order was entered, show the latest price.

[RalphL]

As I said you have to use the buttons. My mouse wheel doesn't change the record. This was not a final design, just a concept. You would use lookups to put the price in the lineitem. All I wanted to do is show you another way to do what you wanted without all the overhead of your method.

[sbg2]

You don't have to use a value list to choose products for your line items. You could use a portal to list all products, and another portal to list the line items for an order. A user could click a button on the products portal to add it to the line items. Once the list of products got large, you could add filters to the portal to limit records shown by category.

Screen real estate is a big issue, I wouldnt want to take up more room listing all available products in a seperate portal. Needing to click on an item to add it just adds another step that my users, and myself, would rather not do. What happens when a user accidently tries to add an item that was already added? I could use validation to pop-up an annoying message, but why go through this when I could let the program add the items rather than the user?

If you really want all the products to show up all the time in the line items portal, you could run a script each time an order is created to make a line item for every possible product where the initial quantity is zero or null. The user can just enter a quantity for the items to be ordered.

Create a loop in Items.fp7 that runs a script to add a new record in OrdersLineItems.fp7 for each product in Items.fp7? Isn't this the same as exporting all items from Items.fp7, then importing them into OrdersLineItems.fp7?

It seems like the way you're doing it now, you'd have to change the layout every time a new product is added... wouldn't that get to be kind of a pain? And wouldn't the layout get large/unwieldy if you had a large number of products?

I can leave room to expand if I wish so i wouldnt need to change the layout, but new products are not added often so at present this is a non-issue.

[sbg2]

Dont get me wrong, the solution you provided is very close to what I want. I might be able to get around the mouse scroll issue by only using the A_Detail (order entry layout) for its intended purpose... order entry. When the users Adds a new Order the new record would be the only record displayed (much like the way i have set-up my system now). If a user wants to browse or find orders they would do that on a different layout. However, I'd be concerned that my curious users might try do a Find or Show All Records on the order entry layout which could be dangerous. As I have it now, if users perform a find or use the book (or mouse wheel) to scroll through records the correct information is displayed on the order entry form.

We are a small office and 2 of the 4 people that use the system do not use it very often. Telling them not to perform a Find or Show All Records could not be trusted to work. I cant lock them out of the files either because they definately need to use them from time to time. Is there a way to disable Filemakers Native Menu commands?

I would also have to solve the issue of the extended price not updating for line items that have have changed from a null value to a qty.

[Reed]

In the privileges section of the accounts and privileges dialog, there is a menu for controlling access to filemaker menu commands. There are only three choices, but one of them is no menus. You could have the file open by default to an account that has no access to menus, then if someone who knows what they're doing uses the file, they can hold down the option key (shift on windows) while opening the file to use a more privileged account.