Inventory Field Updating from Data Entry on other fields

[sicSRT8]

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?

[comment]

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.

AvailableRolls.fp7.zip

[sicSRT8]

I noticed on the Relationships menu Usage and Allrolls have a link together but show an x instead of a =. What does this signify?

Is there a guide that goes into great detail on HOW to use each piece of formula. I saw the definitions but there isnt a guide to how to place them properly.

Edited January 28, 2009 by Guest

[comment]

1. See:

http://fmhelp.filemaker.com/fmphelp_10/en/html/relational.11.8.html#1028014

2. That's why this forum exists.

[sicSRT8]

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?

Thanks for you help!

[comment]

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.

[sicSRT8]

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.

[comment]

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…

[sicSRT8]

Sorry I meant 10 diff stations on one print line. Print, UV Coat, Back UV Coat, Laminate, Cut, Encoding etc..

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

[comment]

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.

[sicSRT8]

Thats what i didnt want to happen. But trying to describe why i dont want things jumbled and have them the way i do. But im not getting the point as to why its the wrong layout.

[comment]

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.

[sicSRT8]

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.

Thanks for you extreme help!

[comment]

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

No - you would have a record for each usage in the Usage table. Take a look at the attached.

AvailableRolls2.fp7.zip

[sicSRT8]

Thanks Ill take a gander thru it see what im doing wrong..

[sicSRT8]

I added a layout for what my roll usage form fields look like.

may help with explanation.

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???

addedexample.zip

[comment]

I'm afraid that in order to answer all these issues I would need a much better understanding of how you operate - and that could take quite some time.

Do, however, take a look at the sttached idea:

addedexample2.fp7.zip

[sicSRT8]

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.

[sicSRT8]

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.

[comment]

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.

[sicSRT8]

I have it set to auto freeze but cant get the column two or "Roll #2" Starting feet to show up.

Anything FM is above my expertise... thats why im here... Yes you are helping the example worked im just taking it deeper.

Im able to select roll and see the feet in the drop down from inventory. Now i just need it to show for Roll 2 which thinking it may be a relationship issue.

[comment]

Yes you are helping the example worked

No, I don't think it did. Because you said:

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.

[sicSRT8]

No it works and know how it works but im adding to it by showing the amount of roll in separate field as with the drop down which already house that info.

[sicSRT8]

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.

Edited February 11, 2009 by Guest