Inventory Tracking Database - Comparing shipping & receiving data

[Ryan Prager]

For some time now I've been developing an inventory tracking database specific to our company's needs.

I have had a hard time with a specific portion of the database. I'm hoping somebody can point me in the right direction.

Specifically right now I have two different tables, one for shipping and one for receiving. (Every week we ship out equipment...laptops, cases, switches, printers. These all have specific asset tags which are scanned into FM with a barcode scanner when shipped or received) Shipments are tied to a deal number as well as received equipment.

I need a way to compare what was shipped with what was received for a particular deal. Here is the kicker. There can be more than one shipment for a particular deal and the same goes for receiving.

Also I use repeating fields for the each piece of equipment. For example, a particular shipping record could have 50 repeating fields for laptops and so forth. This allows me to export all of the data into a separate tracking list breaking up the record into separate records based on asset tag. I'm not sure if there is a better way to do this.

Anybody have any ideas how I can compare what was shipped with what was received and report the differences in data. Thanks ahead of time for the help.

[Søren Dyhr]

I'm not sure if there is a better way to do this.

I am! It's unhealthy to use repeaters anything else than utility purposes, you're having human understandable data stored in repeaters which is a flaw in your approach, which will cause grievience than joy, so roll up the sleaves and learn about how relations are implemented in filemaker.

Then should you explain why you have chosen two tables without a shred of relational approch, which here ideally could be one single table named transactions ...and be build on a relational approach instead.

--sd

[Ryan Prager]

Thanks for the reply but I've been working on this database which is very relational for quite some time now but using repeating fields seems like the only way to me. I've tried other methods and nothing seems to work very good for this particular situation.

I have a laptop field with 70 repeating values because we could possibly send out 70 laptops in one shipment. Are you suggesting that I make 70 different laptop fields? At this point I am confused as to the best approach.

I've heard time and time again that we should do away with repeating fields unless absolutely necessary but I'm having a hard time finding a way around this one.

How do you suggest transitioning away from repeating fields in this case?

[Søren Dyhr]

I have to ask is it because you have individual seriesnumbers attached to each labtop you choose to use repeaters?? Or could it be accomplished by adjusting the warehouse figure on each exit and arrival ...even so would it be possible with a portal full of records.

Perhaps it would be beneficial to see the scripted way you make the arrivals and the exits in the repeating fields that a seemingly tough to do without repeaters. My guess is that the scripting will be simplified quite a bit by normalizing enough.

You can't call your solution relational, before one thing is eastablished ...namely one atomic fact per field also called 1 NF

--sd

[Ryan Prager]

Thanks for the help thus far. I'm always looking for the best way to go about something in FM.

Each piece of equipment has an asset tag with a serial number. Here's is why I've been using repeating fields.

Each shipment or received shipment has a deal number tied to it.

We always send multiple pieces of equipment (laptops, cases, switches, printers) with our shipments. There could be 50 laptops, 10 cases, 5 switches, and 1 printer in one shipment.

On this form, using a barcode scanner I simply scan in each piece of equipment in the correct fields. Each time I scan, FM tabs to the next repeating field.

This record is then exported to a transaction list breaking up each repeating field into a separate record.

The same basic process is done when equipment is received.

Is there a better way to do this? Right now my scripts aren't too bad. I just have a lot of importing and updating of records. I hope this helps.

[Ryan Prager]

Also, because the fields are repeating I'm able to get easy counts of how much of each type of equipment I'm sending. But again, I'm not sure if there is a simpler way.

[Jeff Bills]

Instead of using repeating fields, is it possiblt to set up a script to change your quantity from X to X + 1 . . .

Set Field [Quantity; Quantity + 1]

I've never worked with barcode scanners so I don't know if this will work but it would be better than using repeating fields. What happens if you only have 70 repeating fields but have 100 items coming in?

[Søren Dyhr]

On this form, using a barcode scanner I simply scan in each piece of equipment in the correct fields. Each time I scan, FM tabs to the next repeating field.

My knowledge to scanners is rather limited, but I seems to remember that the way it works is number -> chosen delimiter -> next number.

If say we use this for filling a multiline key field, would a special special layout be required to aviod the scanner stuffing values in arbitrary fields on it's way.

Should we get matters established here, we should be dealing with at many 2 many kind of relation, so what could take place is a multiline key should link order to items. Please excuse my accusation if it's wrong, but I find no traces of regarding this as a many 2 many relation? But wouldn't it feel pretty inconvenient to live within the constraints of say 70 repeaters, if a customer orders 71 items.

Next issue is what if we during our scanning discover an item not quite in mint condition a few seconds after it have been scanned, when we meanwhile have scanned 10 succeding items, how do we get rid of the one we can't ship?? Again are repeaters inconvenient, compared to a straight forward textfield acting as multilinekey, you can even enter stuff in a temporary buffer field before desiding to make the invoice.

The summing of the items send is hardly a problem, the printout could be a subsummary report grouping on itemtype, and establishing whats left in the warehouse is just merging token id's in all invoices via ValueListItems( -calc, acting as primary key for a tetha join unequal, and you have instantly via a multi criteria a figure telling you how many of a certain type remains.

One nifty CF exists to deal with entries in text fields, and similar straining a text field for unwanted items:

http://www.briandunning.com/cf/39

...which makes the scripting very very simple, I would gladly pay attention to a similar CF doing this with repeaters???

--sd

[Ryan Prager]

Okay, now I really feel like we're getting somewhere. Well I at least feel like I'm learning some very important skills.

I don't have any experience with mulitline key fields as you talk about.

I agree that living within the contraints of repeating fields could become inconvenient.

Also, by shipping I don't mean shipping orders to customers. We simply ship equipment to our employees at a job. Once they are finished with the equipment, they return it to the office.

I'm having a hard time understanding some things that you talk about as you are clearly much more experienced than I am. I can't seem to picture what you are all describing.

Do you think you could put together some type of example so I can understand better? I think this would help greatly.

These are just a few terms I'm having a hard time understanding:

mulitline key fields

temporary buffer field

merging token id's

Also, thanks for the link. That custom function could prove to be very helpful.

[Inky Phil]

Ryan

I posted a sample file here

http://fmforums.com/forum/showtopic.php?tid/179102/post/216906/hl//

that demonstrates multi line keys

HTH

Phil

[Søren Dyhr]

These are just a few terms I'm having a hard time understanding:

Perhaps you should look at this template:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

[Ryan Prager]

That demo file was very well done but I don't see how that might help my situation. Maybe its the scanning part that is making things difficult.

I just wish there was an example of how I could go about this project without using repeating fields.

SD,

Everything that you were describing in your previous post seemed to make sense but I really need to see it in order to fully understand it.

[Jeff Bills]

It would seem to me (a newbie also, remind you) that you need a record for each asset rather than one record (for computer A, for example) holding repeating fields.

You end up with say, 20 records for computer A, 57 record for computer B, and 87 records for . . . . each having their own asset id.

Each record can have ship/receive field(s) and a "job" field describing it's destination. Maybe the barcode reader can trigger a script to change the ship/receive field.

Food for thought . . . and I'm hoping I'm not adding confusion

:(

[Ryan Prager]

Jeff, thanks for adding your comments.

I have tables that have each of my assets (laptops, cases, switches, printers, other).

I have another table for shipping and another for receiving. Each record within shipping or receiving has all the equipment that was either shipped or received.

I've attached a picture of a simplified layout I use for receiving equipment. You'll see my use for repeating fields. I hope this helps.

[Søren Dyhr]

Alright here is a template, that is sort of made ready for a scanner entering values in a global field, if that should be the requirement. But similar are the lines in each of the portals "Booked" and "Availiable" clickable - so you are able to pluck one item in both and make them exchange places respectively.

But what's going on here is a many 2 many relation without a join table, and each item is stored as individual items, regardless of type, but the portals sorts so the listing in each will group things typewise.

If your layout however makes particular sense to the users will you be able to make cut up made vertical portals sans scroll each having their own relational key based on type constant and the multiline key!

Where entering something in a cell by eyeballing an empty slot, as in your approach are going to be unreliable since you without any kind of warning are abel to make double bookings of the same kind of equipment, instead you have to rolodex or search thru all records to watch out if an item is occupied. It's more or less you problem with the unrelational approach you have - and probably the reason for raising this topic??

You have a too paper-near metaphor, not quite recognizing what a database structure is able to do for you querry wise and are only using the flipside of the coin, the structural organization of the data.

I've tried to outline a similar layout, but without any of the repeaters, if say your users insists??

--sd

test.zip

[Ryan Prager]

SD,

Thank you so much for all of your help. I have really learned a lot the last few days. I am going to be restructuring my application.

Your test file really has helped alot. I've been going through it to make sure I understand how everything works and then incorporating that into my system.

I've never dealt with global fields much at all. To me, your test file is quite complex. I'm realizing that I still have a lot to learn.

I will continue to post if I have further questions.

Thanks again for all your help thus far!

[Søren Dyhr]

I will continue to post if I have further questions.

Please do!

--sd

[Ryan Prager]

Okay, so I started to implement your test app into my solution.

For some reason I couldn't get the items that were in the available portal to be removed from that portal when moving them to the booked portal. Although it worked when moving from booked to available.

Also, what is the function of the ReceiverReturner field?

One last thing, my app would not put values in the calcavail or ReceiverReturner field? I checked and double checked all the calcuations and scripts. There's probably just one small thing that I'm missing.

Thanks

[Søren Dyhr]

Post me a clone of the template, and let me investigate!

--sd

[Ryan Prager]

Well I went back one more time and checked your sample with my implementation of it and realized that my CalcAvail was stored. This makes sense why it wouldn't be removing items from the booked list.

Also, what is the purpose of ReceiverReturner?

[Søren Dyhr]

Not anything yet, but it's kind of preemptiveness since you're likely to get a unique ID in there over time since, for say a name like John Smith are very likely to be a dupe, call it employee or staff ID ....

Sorry for the confusion caused!

--sd

[Ryan Prager]

SD,

Okay, so I have few more questions. I now have my implementation of your test working in my environment to a certain point.

I want to track each and every transaction of sending and receiving. Originally I had a listing of all transactions. When I was using repeating fields, each field was broken up and exported to another list. How would I do this in the new format?

Also, I have seperate tables for each of the different types of equipment that have asset tags(laptops, cases, switches, printers, ect.)I would like to have one table that has every asset. Is there a way to have one table grab all records from multiple tables?

For instance, if we buy 10 more laptops, I will make 10 new records in the laptop table. I would also like these to be created in another table that has all assets. And if I delete a laptop record, it should also be removed from that table as well.

One last thing...I want to be able to know the current status of any piece of equipment. I'm not sure how I can do that with this new format. Hope you can help.

Thanks

[Søren Dyhr]

I want to track each and every transaction of sending and receiving. Originally I had a listing of all transactions. When I was using repeating fields, each field was broken up and exported to another list

Yes there is indeed an issue with the solution I gave you which only deals with status quo in a library wise manner.... Checkout Comments uploads in this thread:

http://www.fmforums.com/forum/showtopic.php?tid/168633/hl/transactions%7CComment/tp/0/all/1/

The merger of the two, mine and his principles is pretty abstract thinking. A cheating method would be to track modifications in the assignment field via:

http://www.filemakerpros.com/CHANGELOG3.zip

...It would be cheating because it wears a syncronization issue as well as storing in a 1NF breaking way, it should broken into be individual records.

I would like to have one table that has every asset

It's not relational healty ...or rather it's a eagerly debated topic:

http://network.datatude.net/viewtopic.php?t=107

...I lean towards the attitude where its considered a flaw in the reasoning. However could a table merger be dealt with in this way:

http://previews.filemakermagazine.com/videos/544/SavedSearches_full.mov

...taking care of deletions and additions to the roster. Another method is to make each table a browser, for a joined table with several foreignkeys - one for each browser table. The thing David Kachel calls Tiered tables in his Whitepaper...

One last thing...I want to be able to know the current status of any piece of equipment. I'm not sure how I can do that with this new format. Hope you can help.

Thats much much easier, just make yourself a portal in the TheItems table in my template showing data via the User-relation utilizing the bidirectionality.

--sd

[Ryan Prager]

I feel like I've moved foward with certain things but taken steps back with others because for the most part it sounds like FM can't handle what I need my app to do.

As much as I hate to say this, it almost seems as if I should go back to repeating fields where they can be broken out into separate transactions.

Doing that would make it impossible to compare what was shipped and received. I am confused and almost feel like I should find a better solution for this project.

I'm going to keep trying to come up with something but right now I'm at a loss. I know that FM has the ability to do what I need it to do but the solution doesn't seem as clear as it should be.

[Søren Dyhr]

I'm going to keep trying to come up with something but right now I'm at a loss. I know that FM has the ability to do what I need it to do but the solution doesn't seem as clear as it should be.

And I may have missed along the thread that, what you did with the repeating field is the transaction model as such ...It's the drag with guessing the reasoning behind a solution. Where we as usual are dealing with tight spots in the relational model utilized, that very well could be barking up the wrong tree and even do it several times.

I'll re-read the thread tomorrow, and see if I can get the things straightend!!!

--sd

[Ryan Prager]

Not only did I have it break each repeating field into single records for each piece shipped but I had it look up the asset tag (in a table with all asset tags) and change the status of each piece of equipment.

Because everything was in a transaction list, I could create portals to show me everything that was shipped or received for a particular deal.

I'm now working with FM 8.5 and have found new life with the "new" list function. Using the list function and the XORvalues function that you recommeneded, I'm able to compare a list of what was shipped with what was received while unfortunately still using repeating fields. The list function works great with repeating fields.

I look forward to your response. Thanks for all your time spent thus far on this issue.

[Søren Dyhr]

I have now implemented a transaction table using the Luca di Pacioli method for ledgers, which can be studied by tearing this template apart:

http://www.filemakerpros.com/LULAST.zip

...but instead of adding to the balance, do I use XORValues since it isn't a number we're dealing with but a string of occupied items.

The transaction table is by this method fully searchable so the tracing of an items movements back and forth between a certain customer is posible and even within a certain time slot if a field is provided for it.

What the template still misses is the prevention of the same item beeing booked again or twice or rather before a return transaction have taken place. But this is covered in the previous template, there is nothing difficult here ...just a valuelist gathering the itemID's in use regardless of the user and the non equijoin to strain the list...

Finally haven't I dealt with the transactions seen from each item's record yet. It seems to be straight forward!

--sd

Transactions.zip

[Søren Dyhr]

I couldn't help it! I just had to continue ...which means the two outstanding issues are done as well...

--sd

FurtherTinkering.zip