Inventory w/ Serializable & Non-Serializable Items

[VTzeroone]

I am making an inventory for the Boy Scouts. We have the database all drawn out, but I have run into a problem: I need to have a general index of all items. Every item is either serializable (which means we have a record of each item, like Helmets, where each helmet has specific information stored about it), or not (like Caribeeners, where we would store more generic information like how many on hand, price per unit, etc.).

I have a file for serialized records, and one for non-serialized records. Then I have a file for the master list, which only has fields for serial #, name, and part ID (the part ID is unique for all the items, whether it has a serial or not. That is the field I am using for my relation).

The problem is, when I enter information to add a record for a new serializable (or non-s.) item, I need it to also create a new record in the master parts list to go with that item. Otherwise, the master parts list is not complete.

edit: The part ID will be my match field in my Master->Serial and Master->Non-serial relationship. But I guess I really want to know how to make my database say "upon creation of a record in file a, create a record in the related file b with specified data in its fields"

[LaRetta]

Hi VTzeroone,

I would tend to have both Serialized and Non-Serialized data in the same dB and just add a field TYPE. However, if the fields in your two

[VTzeroone]

Thanks for your help.

So if I type something in the serial field it will create a serial record and then I can type in all the other serial fields. Then if I leave that blank I can type in any of the unique non-serial fields and it will create a non-serial record?

Since when I finish this I am handing it over to the Scouts for them to manage, I want this to be as easy as possible for them. Is there a way to hide the unique serial/non-serial fields until, say, they either check a box that says "serial/non-serial" or something to that extent?

[Fenton]

I don't understand why you have 3 files for these items. If you're going to have a record for each item in your "master list," then you only need 1 file.

If you're going to have something like "Helmets" in the master list (only once), with several individually identified helmets, then it's 2 files. You only need a 2nd file for multiple individually identified instances of an "entity" (not quantities of an entity).

The Part# for an item (or any "user entered" text for that matter) should NOT be used as its "serial ID." Serial ID is a special kind of field in FileMaker, auto-entered. The word should not be used for anything else. The Part# is simply a field. Important in its own way, but NOT the serial ID.

So, each item, whether in the master file or a child file, should have its own unique auto-entered serial ID. If you have and enter child records in a portal, with "allow creation of related records," then the serial ID of the master file will automatically be transferred, no matter what OTHER field is typed into.

A new auto-enter Serial ID for the child file record will also be created. These are 2 separate fields. The masterID is only a "foreign key" in the child file, not an auto-enter serial ID.

You seldom type into a serial ID field, unless it's a drop-down value list (you may also set it with scripts). You don't even have to show it anywhere, except your own developer layouts.

So you can show the Part# in the portal. If an item has one, fine, type it in. If not, also fine, just type in the Name. You can make the Name a Lookup field, based on a relationship back to the Master file. That way you only have to type the Part# and the name fills in automatically.

If someone needs to Find something, they can type either in the Part# or in the Name. In which file? I don't know, 'cause I still don't see why 2 files :-?

[Fenton]

I wrote:

You can make the Name a Lookup field, based on a relationship back to the Master file. That way you only have to type the Part# and the name fills in automatically.

Actually you should make them BOTH Lookup fields. Just type in a Quantity, and both would lookup, based on a relationship "foreign master ID::SerialID" from the chilc file back to the Master file. (If there is a child file :-?)

[LaRetta]

Hi Fenton,

Yes, they can be combined and he was given that information. He has chosen to keep the existing structure (3 dbs). The reason for three dbs is because there are two 'types' of parts, whose fields are quite different.

If the Serialized and Non-Serialized 'different' fields were in the same db, it would increase the file size because many fields would be left blank in each part record; and we would be dealing with additional validation issues so his boys don't insert data into the wrong field, etc.

The Master is his parts db. The related files (Serialized and Non-Serialized) will only contain those unique specific fields. And it follows database theory.

Hi VTzeroone,

So if I type something in the serial field it will create a serial record and then I can type in all the other serial fields. Then if I leave that blank I can type in any of the unique non-serial fields and it will create a non-serial record?

Exactly right. Any time you type into a related non-key field (if 'allow creation of related' is checked), a new record will be created if one doesn't currently exist that matches your Main key (PartID). You can script it, but why would you want to!

Your request to show/hide the fields is known as the 'visibilty trick.' I can explain how to accomplish that when I get back tonight but I'm out of time right now. It involves using calcs to break the relationship the portal is based on.

And please don't be confused about Fenton's comments on your Serial#. You and I understand that your Serial# is the actual serial number on the part, and your unique ID is your PartID (for your joins) and that works very well. You can call it anything you wish.

LaRetta

[VTzeroone]

Yea, he's talking about the serial feature that auto-enters and a serial number for something (like for my Part ID), but you're right, it is a number the user will be entering for items that have their own serial number already.

I will try to look up the visibility trick, but if you have time and don't mind feel free to explain it to me.

One more thing - this inventory is for the Blue Ridge Mountain Council of the BSA - so they have many items in many different places. Each location has different programs. So like within Camp Powhatan, you have Cope, Waterfront, Climbing Tower, etc. If I made fields in my master list for "Camps" and "Programs" would I be able to create a way of browsing the database by doing the following?

- Show a list of all camps (by scanning all the camps represented in the master list)

- The user clicks on a camp

- Show a list of programs in that camp (found same way as the camps)

- The user clicks a program

- Show a list of items at that location

[EddyB]

Hi VTzeroone,

Please see my post in the Samples forum on Date Calculations Example. This example is one where you enter information into a field and other fields "magically" appear.

If you need anymore help on this give me a shout.

Ed.

[VTzeroone]

Thanks Ed, that looks like what I am looking for. http://www.databasepros.com/resources.html is having issues right now with their server (can't search for anything), but I'll check back there later.

[EddyB]

Hi, if you download my calcs example you should be able to pick it up from that. Or if you like I have attached the Database Pros file on here.

Ed.

MoreVis.zip

[LaRetta]

Hi VTzeroone,

Okay, you asked about visibility. I see Eddy gave you a link (thanks Ed), but sometimes it's easier if it's listed out, than having to figure out a demo so, just in case, I'll list the steps out for you.

The visibility trick happens with portals. You can place all your Serialized fields within one portal and they will not show (the relationship will break) when Serialized is not clicked.

Create a global text called gType. Define new value list (Custom) called Type and enter 'Serialized' and 'Non-Serialized'. Place gType on your layout. Attach your new Type VL to it as a radio button. Resize if necessary.

For Serialized (repeat for Non-Serialized):

1) Create a calculation cTestSerial (number, unstored) = gType="Serialized"

2) Create a calculation cPortalSerial (number, index ON) = 1

3) Create a new relationship called Portal Serial:

4) Join Master:cTestSerial to Master::cPortalSerial

5) Create a portal based upon Portal Serial.

6) Place your Serialized fields within it.

7) Change the portal fill pattern and pen pattern to transparent.

8) Click the Serialized radio button and watch the magic!

You also asked about finding and filtering the display of records based up selections in your Camp and Program Value Lists. There are many ways to accomplish it. You can use a find, or a 'find without a find'(portal filtering) which involves a self-join, portal and globals. You might also be interested in Conditional Value Lists. Each technique involves a bit of description and is really off-top to this thread. I suggest you search and review in Portals and Finding. Once you have a better idea of how you wish to accomplish it, post a new question in the correct section if you get stuck.

As you can see, there are many great people on Forum willing to assist!

LaRetta

[Ugo DI LUCA]

Hi,

If I properly understood the whole thread, you have two kinds of items :

- Groups of Items "Helmets" which has a Unique_ID (FM Based) with 50 different kinds (size, colors,...) which you have given them a Unique Code (not FM based).

- Unique Items "Caribeeners" which hasn't a Unique Code but has a FM Unique_ID.

You actually have 2 files (one for your Groups, one for your Unique Items) and a latest file which joins the 2 previous cited, that you finally use as your Master List.

If all of this is correct, then you don't need 3 files....

You just need to "categorize" each items (member of a group or unique).

Then use either :

- The Visibility trick to hide the info you don't need

- Separate layouts for Groups and Unique Items (using your own status bar to dynamically jump from one to another while moving through records)

Surely the Visibility trick is best.

I've done this with a bunch of inventory dbs where some items could be part of packages (2 files needed there though).

Even the button that leads to the package description is hided with the Visibility Portal Trick.

I don't even know if my remarks aren't redundant with what has already been posted here. I was confused as Fenton with your "serial"...

[VTzeroone]

Thanks for the help. I'm sure that I can figure out that visibility trick as soon as I look into it in more depth.

Ugo - the whole serial thing is that there are two types of items.

1. Generic ones - we don't have info on each individual item. So records for these items would look like this:

Caribeener, Qty on hand: 50, Qty needed: 55, Notes: ____

These are non-serialized because we are not keeping track of each individual 'beener.

2. Specific ones - Each individual item is recorded in the DB. We use its serial number (something inscribed on the actual object) to identify the item.

Canoe - Serial # 33545643, Condition: Good, Purchase Price: $400, Supplier: ABC, Operable? Yes, Purchase Date: 3/5/03, Notes: _____

Hope that clears things up. I should be ok w/ this DB for a while.

Thanks

-Ryan

[Ugo DI LUCA]

Well,

A single ItemFile.fp5 and the visibility trick to hide or show the extra fields with a calculation would do the job here.

c_hide (num-indexed) = Case(IsEmpty(' yourItemSerial '),0,1) or notIsEmpty(' yourItemSerial ')

and

c_index (num calc =1, indexed)

and VisRelationship --> c_hide::c_index

For filtering process, you'd create an extra calculation

c_filterKey (indexed) = Choose(c_hide, "generic","specific")

In any related file where you need it, create a Value List "CategoriesVL" and choose ' values from field ' --> select c_filterkey.

Then add an extra field 'g_category' (global text), attach the new value list to this field and create a relationship

FilterRel = YourRelatedFile: g_category::ItemFile.fp5:c_filterkey

to show the result of the Filtering process in a portal or perform a GoToRelatedRecord script step.

[VTzeroone]

Thanks guys, I have this thing ALMOST done! The visualization thing works just like LaRetta said.

The last thing:

I have a file for camps, and each camp contains a portal of locations within that camp. So the camp Powhatan contains the locations "Waterfront", "COPE", and "Tower".

In my master list of parts I have a drop down list for the user to chose at which camp the item is located. I then need a second location drop down box that lists the locations for the camp that the user has just selected. So if I select "Powhatan" from my camp list, then the 2nd location drop down menu contains the choices "Waterfront", "COPE", and "Tower".

I think after this is done I will be just about finished!

Thanks

-Ryan

[VTzeroone]

Solved my own problem! I changed the relationship between "Camp" and "Program" (the location within the camp) so that the relationship used the camp name instead of a camp ID. So each record of programs also had a field for the name of the camp they belong to.

Then I added a relationship in the master list from master:camp to program:camp so it would match up the camp name that the user chose with all the programs with that camp name. Then it would just get a list of those programs using the list I set up.

[VTzeroone]

Any way to get the record to remember which radio button was checked? If I select "serialized" then all my records have "serialized" selected. I can switch back and forth and show/hide the fields but I'd like it to remember which is which.

[VTzeroone]

fixed that too -- just changed the gType to text instead of global.

[SivadPT]

Hello

I tried the solution you have here on something I'm doing.

The instructions were very clear and I manage to do it.

My problem is that I can't use the portals because I have a lot of data to input.

I decided to go to the files A or B using scripts and the enter the data on several Layouts.

Again I have a problem I use FM Mobile to enter some data, and I can't see How I can enter the data for the master and for file A or B using the Palm.

The best way I can figure it out was to have only the two files A and B (forget master)and load them on the PALM. Big problem now I can't have only a serial number (SN) for the two files.

Does any one knows if it is possible to FM to create a new record on A (or : and before it enters the SN it "asks" the other file if it's serial number is lower or higher and them it enters the next unique SN?

Thanks

Sivad