Inventory Field Updating from Data Entry on other fields

Recommended Posts

Ok this one has been puzzling me. I know its possible but cant get it to work.

I have two tables Inventory and Usage

I input inventory paper roll per record. In that record has all the info i need on each roll. Lot number, FT on Roll, Date Received, etc.

On the field in the Usage Table where the Data Entry User inputs amount of ft used I want it to update the inventory.

So say the roll has 1000 ft and the user used 100ft of the roll. I want for that specific roll number to have its ft on roll updated to show that 1000 minus what was used.

Also in the usage table. I want the user to select the roll number from a drop down of which will show rolls with >0 Ft on them. I just dont know the correct way to formulate this calculation either.

Can you default inventory item fields to auto populate 1000 and then when the user inputs the usage deduct without throwing things off? Each Roll has a specific roll number and never duplicated and entered in via import excel spreadsheet. The ft on certain rolls are manually entered and certain rolls are default 1000 ft.

Suggestions on parts welcome. Im a designer not a programmer so I am VERY new to formulas. I dont somewhat understand them but learning the correct way to use them. Also if you have cheat sheets on forumlas or links to them please share.

Thanks everyone for your help. Without this forum I would be LOST.

PS I currently use FM 9 but waiting on approval to upgrade to 10 advanced will anything get thrown off when I do the upgrade?

Share this post

Link to post

Share on other sites

I am afraid you have picked a rather difficult issue, one hardly suitable for a beginner. The problem, in a nutshell, is that a roll's availability depends on related records and cannot be stored - thus making it difficult to base a value list upon it.

There's a workaround called the "Ugo method" (see attached), but it might take a while to understand how and why it works.

Share this post

Link to post

Share on other sites

I have gone thru the example thru everything to understand it better. I have made it work on the DB that I need on.

Few Questions...

In the usage table layout when you select the roll and it gives you the ft and allows you to edit and updates the inventory. Everything works great.

The form that I use has three coloumns Initial Roll ,2nd Roll (If we need another roll to complete job, and 3rd Roll (Same thing).

In table fields I created a field for each "IntRollID", "2ndRollID", "3rdRollID" So that when i pull a report on that specific job number it will pull the correct roll info and when they were used on that job. I know its tedious but oddly enough need the report to have all this info. I could care less how it looks on the layout or fields (As long as I know what they are)

But when i try to relate them etc it pulls the same info up for all boxes when i pick from the initial. Do i create relationship boxes for each one and relate them to rollID# or am I missing something.

If i create a diff relationship box and relation would that bar me from updating the inventory as a whole?

Share this post

Link to post

Share on other sites

The form that I use has three coloumns Initial Roll ,2nd Roll (If we need another roll to complete job, and 3rd Roll (Same thing).

This is not a good arrangement. You should have a separate table of Orders, with the Usage table serving as a join table between Orders and Rolls. The Usage table needs to have both RollID and OrderID fields. This way you can enter a virtually unlimited number of usage instances per order (e.g. through a portal to Usage placed on a layout of Orders), using the same value list and relationships.

Share this post

Link to post

Share on other sites

Then im seriously lost as to how to properly laythis out they way I need it... I have alot of info per job per client and inventory is specific to client as well. I have 10 stations on my print line of reports from each station whihc is why i created a table for each station.

If i jumble up all orders then its going to be a pain in the arse to go back thru a specific client records to change something if need be right?

Im seriously lost as to how things should be layed out. I need a layout field per station of my print line which is why im doing it the way im doing it. One sheet with all the job and inventory info would have 100's of fields on it and leave too much to error which is why im trying to get fm to work for me.

Share this post

Link to post

Share on other sites

If i jumble up all orders then its going to be a pain in the arse to go back thru a specific client records to change something if need be right?

No, not at all - that's what relationships and finds are all about. In terms of layout real estate, there's not much difference between Field1, Field 2, Field3 or three records in a portal. But in terms of handling the data, the latter arrangement is enormously more flexible and useful.

Similarly, having a table per machine means everything needs to be replicated 10 times. And if you add another machine…

Share this post

Link to post

Share on other sites

Its all relevant to each job but all one job info... I know its alot but it has to be documented for everything that happens... If i use one form the guys running the machines are going to mess up more than they already do.

Edited January 29, 2009 by Guest

Share this post

Link to post

Share on other sites

I am sorry but I don't quite follow. This started out as a specific question involving two tables. Now it's rapidly branching out to the overall data model - but you didn't give us enough information for that. I don't even know what a "print line" means.

Share this post

Link to post

Share on other sites

Well, "jumbled" is just a word. Having records of different categories in the same table doesn't mean they cannot be separated at will.

Let me give you a simple example: suppose we have a store that sells some products. We give each customer an invoice that details the purchased products. Our basic data structure is:

Invoices -< LineItems >- Products

Note that:

(a) we do NOT have a fixed amount of ProductID fields on an Invoice; a customer can buy ANY number of products on a single invoice.

(: we do NOT keep separate tables (or fields, or anything) for the three cash register we have (or for the five sales people that operate them). However, each line item "knows" who entered it, on which register.

Having all the line items "jumbled" in the LineItems table does not preclude us from knowing what was sold on each individual invoice (you can browse Invoices, and the relevant line items will appear automatically in the portal), or how many items were sold of each product (same thing from a layout of Products), or producing a report of line items summarized by sales person - and so on.

Share this post

Link to post

Share on other sites

So keeping tabs on say order# 1 and we used three diff rolls to complete that job. I would classify them as Roll_1, Roll_2, Roll_3, in fields under "Jobs" table and still be able to show them on a report while using the same drop down as you had shown earlier in the example.

I went thru it and made it work for me on my end same way but when im on the layout and trying to enter a record. I have to create a diff relationship per Roll# for it to be able to use the drop down for diff rolls and quantities. However I assume this is not the proper way to lay it out. If it is I cant for the life of me figure out how to get the proper quantity to show up for roll 2. But i can get it to drop down the available rolls with the right quantities but cant get the new quantities to show up. Does that make sense.

Share this post

Link to post

Share on other sites

Keep in mind I do have a job entry page where we create the Job number and whne in the usage page select the corisponding job number at top (Not Shown) and then enter the fields in related to that job number.

If i needed a specific relation between all fields and jobs it would be the Job number.

CAB02009 or BAD02009 for example and by that i know what client and what job when i need to go back to specific job. I assume it should be based of that???

Share this post

Link to post

Share on other sites

I guess after thinking about this yesterday is that if i have the one form for all clients is how to keep the records sperate from one another. Meaning that say they select a client from drop down and enter the proper job number. When they go thru the form entering all the specific data when i pull reports will I be able to report eveything based on client then itemized by job number showing the Roll1, Roll2 etc about that specific job. In my head i know it should work like this but having a design background leads me away from laying out like this.

I just need different layouts for each station on the print line that is the only thing I really have which was why i created layout for each client.

Im starting to feel I made this incredibly complicated and is supposed to be very simple... Again thanks for your time on this.

Share this post

Link to post

Share on other sites

Back to this issue How would I get the fields to work properly on the last layout page.

For some reason Im able to use the pull down to see avail roll numbers with quantities > 0 for each column. However i'm trying to get the fields to show the actual roll starting amount (that wont change when updated) and be able to enter in used and amount cutoff (Same as used but classified as cutoff - considered waste. Then update the inventory.

Share this post

Link to post

Share on other sites

If you want to "freeze" the roll's starting amount, you will need to look it up into a field in Usage. That by itself it's not too difficult, but you also need to make sure that the current usage isn't counted (if, for example, the roll is entered after the amount used).

Frankly, I am not sure I am really helping you here. This doesn't seem to be a trivial project at all. You might be better off hiring someone (not me) to help you, as it may turn out to be above your current level of expertise.

Link to post

Share on other sites

For some reason Im able to use the pull down to see avail roll numbers with quantities > 0 for each column.

Which I take to mean "it works, but I don't understand how or why." And that's not the purpose here. I will gladly answer questions and explain things - but this feels too much like handing you pieces that you cannot yet understand, in the vain hope that you'll be somehow able to put them together into a working solution.

Share this post

Link to post

Share on other sites

Im still having an issue not being able to bring up proper Roll Lengths on the second run and third run roll drop downs.

In the first Run where I sleect the roll I can see the roll number and actual length in the drop down. I select the roll i need and the length shows up in the "rollft" field. On the second run of the day I want to select the roll number with the updated roll info in case it was used on the first run and for the "Rollft" field to show the updated amount from inventory.

I get the first run down how it should appear and as far as selecting the roll with updated ft on it but cant for the life of me get it to show the remaining ft to show in the Second Run "rollft"

I hope that made sense.

:More Explaination to that. In your example2 you set it up to read on one table to show a new line for all entries. Instead of being entries I need three of those rows for each entry to be able to do what they are already doing. So instead of Entry #1 ,2, and 3 and so on... just Run 1 Run 2 and Run 3 each able to use the drop downs like you have them set.