Estimates to invoice, inventory movement. Help much appreciated

[BleauMind]

Hi, my name is Mathieu. I'm relatively new to Filemaker and and new to the forum.

 

Merry christmas to everybody and thank you all in advance for your support.

 

 

I have a layout for estimates with a portal showing LineItems items:

 

Clients------>Estimates---->LineItems<-----Products

 

I use price lookups in lineitems to make sure that if a product price change, it doesn't change in already completed estimates. My relationships are set up so that an estimate doesn't make a change in inventory.

 

Now, the problem is, I'd like to, when a estimate is accepted, check a radio button in the estimate layout and automatically copy my lineitems items, at the price set previously in a new portal in a contract table that will make the change in inventory and eventually let me see what i'm missing and let me order them.

 

I know most people put estimate and contracts(invoices) in the same table, I am also ready to do that if  I can make sure estimates dont change inventory.

 

Can you help me figure the relations and the scripts I need to achieve that? Thank you so much.

 

 

[Matthew F]

Hi BleauMind,  Welcome and Merry Christmas. 

 

It looks like you put some thought into your invoicing system, and that's a good start.  Without more information, its hard to give advice on the best approach.  One thing to consider is what level of detail you want on your inventory.  This will depend on the type of product you're dealing with and your business practices.   

 

For example, if you're dealing with one of kind pieces of art, then each and every item will undoubtedly have its own unique ID and inventory record.  If you're selling tooth brushes then each item might be marked only by its model number, and your database just lists a single record for the entire model, with a field that increments the number in stock.  Similarly, if you're selling tomatoes, you might not even know how many you have in stock, but only how many cases were purchased, and how many pounds were sold.  

 

How do you track your inventory?  Is it all on computer, or does someone go out periodically and make a manual count?  

[BleauMind]

Hi BleauMind,  Welcome and Merry Christmas. 

 

It looks like you put some thought into your invoicing system, and that's a good start.  Without more information, its hard to give advice on the best approach.  One thing to consider is what level of detail you want on your inventory.  This will depend on the type of product you're dealing with and your business practices.   

 

For example, if you're dealing with one of kind pieces of art, then each and every item will undoubtedly have its own unique ID and inventory record.  If you're selling tooth brushes then each item might be marked only by its model number, and your database just lists a single record for the entire model, with a field that increments the number in stock.  Similarly, if you're selling tomatoes, you might not even know how many you have in stock, but only how many cases were purchased, and how many pounds were sold.  

 

How do you track your inventory?  Is it all on computer, or does someone go out periodically and make a manual count?  

 

Hey Matthew, you have no idea how grateful I am that you replied. 

 

I added a screen shot of my database relationship.

 

The language is french, let me try to explain.

 

 

Yellow : Clients

- Client table, with client information

- Client notes, related to client. Shown as a portal in the client table so that I can add multiple notes per client.

 

Green : Estimates

-(Dark Green) This is the estimate section. It's linked to clients, and contains the date of estimate and calculations for taxes to give me the Total price.

-Light Green (Soumission_details), It is the lineItem table, contains the quantity, the lookup of the price of the product in the product table and calculates the subtotal. Shows in the estimate section through a portal. The table occurrences linked to this table (in orange) let me use dropdowns to narrow from Category,Make,Model,ID,Price (Not sure if it is the right way to do)

 

Gray: Contracts

Same fields as Estimates for Contracts

Same fields as estimate_details for contract_details

 

Heres what I'm looking to achieve. Through the Estimate Section, I want to select accepted for the status, and have the program copy the fields from Estimates to contracts and Estimates_details to contracts_details.

Contracts differ from estimates because they will cause an inventory move and $$$$ will be used to calculate my sales and profits. I need to be able to modify the contracts without altering the original estimate.

 

Eventually I'll have a table for inventory, quantity in (Purchases) and out (sales) that will be linked to products.

 

Sorry if I'm not clear enough, let me know if you don't understand something and I'll try to explain better. 

[MikeKD]

Why do you need a separate table for estimates and for contracts?

I've only got very basic skills, but I'd have just used one table and added a estimate/contract field, to flag wether it was an estimate or a contract. (you could change an existing record from estimate to contract, or duplicate an estimate and change the flag to contract.

 

Having said that, your original request is definitely possible create a script to:

Create a new record in the contract table

copying the fields in the estimate table (I think you have to do this one at a time)

Paste them into fields in the contract table.

 

Hopefully, someone smarter than me will jump in to help out with the scripting!!

 

Good luck and welcome :-)

Mike

[Wim Decorte]

Why do you need a separate table for estimates and for contracts?

I've only got very basic skills, but I'd have just used one table and added a estimate/contract field, to flag wether it was an estimate or a contract. (you could change an existing record from estimate to contract, or duplicate an estimate and change the flag to contract.

 

 

Lots of reasons.  Contracts can be renewed without a link to a new estimate for instance,... one contract can go through multiple estimates and it is nice to link the together for review etc.

 

Having said that, your original request is definitely possible create a script to:

Create a new record in the contract table

copying the fields in the estimate table (I think you have to do this one at a time)

Paste them into fields in the contract table.

 

 

Ugh, No!  Don't use copy and paste for moving data around.  Collect data in variables and use the Set Field script set to put data in fields.

[MikeKD]

Lots of reasons.  Contracts can be renewed without a link to a new estimate for instance,... one contract can go through multiple estimates and it is nice to link the together for review etc.

 

Ugh, No!  Don't use copy and paste for moving data around.  Collect data in variables and use the Set Field script set to put data in fields.

 

Would you have to set up a separate variable for each field in advance, or can you move to next field and set up a new variables for that before continuing.

I suppose there's quite a lot more control if you do it the first way - you can modify specific fields, ignore some, put in new data etc..

[Wim Decorte]

Would you have to set up a separate variable for each field in advance, or can you move to next field and set up a new variables for that before continuing.

 

 

You create the variable at the time you take a field's content.

 

A simple script like this would look like

 

Go To Layout[ Data1 ]

Set Variable [ $var1 ; Data1::field1 ]

Set Variable [ $var2 ; Data1::field2 ]

Set Variable [ $var3 ; Data1::field3 ]

 

Go To Layout[ Data2 ]

Set Field[ Data2::field1 ; $var1 ]

Set Field[ Data2::field2 ; $var2 ]

Set Field[ Data2::field3 ; $var3 ]