Recording and selling cut lengths of steel.

[ALAN BEVITT]

I need to design a sales/purchase and product database to handle the following.

Purchase order: record lengths of different shaped steel with cost price,description,quantity and length being the main titles.

Sales : record sale price,description,quantity and length. The lengths sold are usually less than the purchased length.

Product: to show full lengths along with any that have been reduced by sale. Example: 10 lengths at 6.00 mts are purchased and 2.00 mtrs cut and sold from one length. and 5.00mtrs cut and sold from another. How do I show that I have 8 full lengths at 6.00mtrs ,1 length at 4.00mts,1 length at 1.00mtrs?

Any help with how to show the individual cut lengths would be appreciated .

[Wim Decorte]

That's a business workflow question more than a database technical question.   How do the users currently decide from which length to 'cut'?

Your database will have to give them the same choices.

This is very similar to fairly standard inventory control.

Orders are checked against qty-on-hand

Qty-on-hand gets updates as part of the picking & shipping process.

 

Don't try to do this with calculated fields; it will make reporting horrible in the long run.  Use scripts for the workflows and update the 'qty-on-hand' as part of the 'cutting' workflow.

[ALAN BEVITT]

Thank you Wim for your reply. At the moment it is a case of them measuring various pieces untill they find one that is long enough but does not leave a silly short piece. I will take note of what you say about using calculations and look into the idea of using scripts once I have a procedure of events in my mind to amend 'qty-on-hand'

[ALAN BEVITT]

Hi, I am struggling with this project, has anybody got any ideas?

[Wim Decorte]

Walk us through what you already have and see if we can help you from there...

[ALAN BEVITT]

Hi Wim, at the moment I can enter stock in terms of lengths of steel, usually 6.0 mtr /length. From that I can have say 3 lengths and cut  2.25 mtrs off a length and with calculations show that I now have 2 full lengths and a piece 3.75mts left.

I am trying to work out how to save the 3.75mtr piece in such a way that I can cut from it and tabulate what is left for further cutting. By calculation I can remove the 3,75mtr piece from the total in stock which then shows 2 length left, which is great.

If I wanted a 3.00mtr length it would come off the 3.75mtr  (if I could see it in a table or something)rather than off a full length.That's about where I'm at.

[Wim Decorte]

Ok, here goes, very high-level all of this so it will need to be fleshed out:

 

- each roll or item in stock is a record in the Inventory table

- fields on that record: length, remaining_length

- you have another table with records for Order Items, with a field for length_required

- for each order item you show the stock items that have a remaining length equal to or longer than the order item's required length

- user clicks on one of the stock items, script creates an "assignment" or "pick" record, that records the stock item chosen and the length required.  At the same time the script decreases the "remaining_length" field with the required length of the item

So no calculations involved (which would create performance issues in the long run).  This kind of scripted operation that needs to updated different records in different tables is a prime candidate for "transactions".  If you are not familiar with that concept then start here: https://www.geistinteractive.com/2019/04/30/start-filemaker-transactions/

The thing to keep in mind is that you may have multiple users doing this at the same time so you need to make sure they cannot pick the same stock item at the same time.

You also need to allow for people to change their mind after they have picked something and reverse the whole process.

[ALAN BEVITT]

Hi Wim, thank you so much for your time and help. I have made a start on what you have said and working my way through ,learning as I go. Being a ' newbie' it may take a while, but time is on my side and I can now see where I'm going.

[bcooney]

Alan, what you're building isn't simple. Perhaps this site will help: http://filemakerinventoryresources.com/

[ALAN BEVITT]

Thank you bcooney for your concerns,  I have started off with a basic  FM inventory template which is fine for handling whole products or part of a whole product( like selling cable off a drum by the metre). Its the pieces left over that I am having trouble tabulating in such a manner as to be able to search and pick them. I have made some progress with the information from Wim and am working on it. When I get it together a bit neater, I'll show where I have got to. ' no pain no gain' I'm gaining knowledge.