Invoice->line items<->inventory??? will it work?

[Hercynium]

Hi, folks. I'm new here, and to Filemaker in general, and I need some help.

I have a database system that I am working on that is growing in all sorts of directions but the relevant files to my question are Invoice, Line Items, and Inventory. For entering a new order I have set up a layout in the Invoice file that includes a portal to the Line Items file. As each item number is typed into the portal the related info from the Inventory file is looked up and copied into the relevant fields. I then enter in the Qty_Ordered and using a layout in the Line Items file I print out a Picking Ticket for the warehouse help. That all works like a charm, and I couldn't be happier.

However, after an order is picked, I have another layout in the Invoice file where I can update the Qty_Packed field in in the Line Items file and generate an invoice. What I want is to also be able to update the item's status field in the Inventory file using this same layout. For example, a pick ticket may come back with an item marked out of stock, or back-ordered, etc. I want to be able to change related data in the Inventory and the Line Item files as I am preparing an invoice... can it be done?

[Ugo DI LUCA]

Hi,

I want to be able to change related data in the Inventory and the Line Item files as I am preparing an invoice... can it be done?

As first answer, consider separating your "Order Preparation" and "Invoice" Files into 2 separate files, sharing the same line item.

Next, you shouldn't need to script an update as the quantity in stock

should be calculated based on the entries in the Line Item. If this is what you're currently doing, but your total available is false, then the only way (IMO) to update the record would be to enter new values in the Line Item.

You could use an adjustment Layout just for that purpose.

[Hercynium]

Well, I'll explore this route, but it doesn't seem to be a solution to what I am trying to do. I've been playing around with scripts, trying to get the desired results, and I think I may be on the right path, but I was hoping there would be a more direct and elegant method.

What I'm specifically attempting to do is modify a record in the Inventory database from a layout in the Invoice database.

Another route I am about to try is making a layout in the Line Item database... and seeing if I can do it from there. Anyhow, all comments offered are welcome... like I said, I'm new to Filemaker (though I've been reading up on it as much as possible!)

[CobaltSky]

Hello Hercynium,

There are several approaches that might get you to the point that you want to be at. Here is one for you to consider:

1. Create a text field in the invoices file called gProductID.

2. Create a relationship from invoice to inventory that matches the gProductID field to the ProductId field (by whatever name) in inventory.

3. Create a script with the step:

Set Field ["gProductID", "LineItems::ProductID"]

4. Attach the script to a small button at the right of the portal row which displays line item entries in your invoice layout.

5. Create a group of fields sourced via the relationship (as per 2 above) from the inventory file and place them in a 'panel' adjacent to the portal in the invoice layout.

With the above in place, you will be able to 'select' a given line item by clicking on the button in its portal row, and relevant details from the inventory file will appear in the 'Inventory Details' panel, and can be directly edited there.

There are various ways to add 'finesse' such as highlighting the selected row etc but the above will give you the basic functionality. It can also be set up so that clicking into a field in the portal will automatically display the relevant product inventory details in the adjacent panel. This would be done by turning off the 'Allow Entry into field' option for the fields in the portal, attaching a copy of the script to the portal fields (ie defining them as buttons) and adding a Go To Field ["relevant portal field"] to each of the scripts which is attached to a field.

Whether a method of the kind outlined above is the most suitable is primarily a question of interface design, as there are various other options, but this may at least give you some ideas to work with.

[Ugo DI LUCA]

Hi Ray,

Not sure I'm following exactly what you are suggesting....I think that it may be the "Qty_Packed" field that is me !

I understand the method described as it is very close to what I'm currently doing.

Say for example that we're invoicing 16 units of Product A and these units are sold in undivisable packages.

My product db shows that this item is sold in a 8 units/box

So the calculated "Qty_packed" would be of 2.

As it often happens, the supplier decided to move its packaging from a 8 units/box to a 6 units/box, and we didn't had time to update the Product db.

That's where I'm using the method above. Hopefully the Quantity/box is a lookup field in my line items, so altering it in the Product file would not affect the entire line items. When changed from 8 units/box to 6 units/box, I'll hit a back button that would trigger the lookup for that particular line in the line item and therefore update the entire line. "Quantity Invoiced" (calculation based on "Quantity required" (=16)) would change from 16 to 18 (16 / 6 = 2,6666 ---> 3 * 6 = 18).

Now the customer may decide if he wants 12 or 18 units, and we'll edit the "quantity required".

The line item therefore will come with :

Product------------Nb units----------Qty Packed

Product A ---------18 units-----------3 Boxes

I have another layout in the Invoice file where I can update the Qty_Packed field in in the Line Items file and generate an invoice.

As I said, I'm not sure the "Qty_Packed" Hercynium is referring to either is the QtyPacked (Nb units / units/box ) or the Packaging (Units/box). That is also why I suggested to have a "Invoice/Order" Preparation file, to avoid having this whole procedure done at the moment of the "Invoice"...

In his first post, Hercynium also said :

What I want is to also be able to update the item's status field in the Inventory file using this same layout. For example, a pick ticket may come back with an item marked out of stock, or back-ordered, etc. I want to be able to change related data in the Inventory and the Line Item files as I am preparing an invoice... can it be done?

Well that is where I'm lost. If the status field show "out of stock", it's surely is a status calculated field related to the line item file. Thus, you cannot edit it or you may have to use an adjustment line in the line item.

Does it make sense or am I still confused.....after my Post # 1,000 !! Yeah

[Hercynium]

CobaltSky, I think your suggestion is what I'm looking for... I hadn't thought about using a global field and a second "panel" (I think I understand what you meant) Actually, it sounds like a great little feature as this way my employees can read the extended description (and eventually see pictures!) to customers as they phone in an order!

And Ugo, Thank you for your suggestions, though I think you're just getting ahead of me! Since we're currently such a disorganized place (right now) we don't have any real inventory so I'm not bothering to keep one in this version of the database! Each item is only known to be in stock, out of stock, or on-order... But very soon we'll start trying to keep count by multiples of Pack or Master Case... Qty_Packed is probably more accurate as Qty_Shipped!

Well, it's back to work for me. Thanks for all the advice!