Checkboxes for inventory

[arjayefem]

I am putting together a database to input orders. I can't find help on how to format checkboxes to get them to show the correct data in both a data input form, or the associated table. here's the deal:

I have a table storing data (yes or no) and a form for people to enter the data. They have a field at the top of the form in which they enter the product number and up should pop the description and whether or not it is in stock (in the form of a single checkbox for each size--size0, size1, size2, etc.) using the associated table's data. There are 12 different checkboxes on the form...

They are all number/boolean fields and checkbox format. How do I get the existing data (ie, checkmarks in the checkboxes) that is stored in the table to pop into the form when typing the product number in to the entry form. this must be simple but I'm missing something.

Next, how do I keep from adding new records when editing the inventory? In other words, I don't want to add any more records when a size becomes unavailable, just uncheck the checkbox and reflect it in the table for that particular piece.

thanks in advance for the help.

[bcooney]

The quick answer is probably to point you to the lookup entry option.

However, I think you might need to revise your table structure (data model). For example, it seems that you are tracking inventory with a checkbox? No, receipts or items sold? But apparently you do have orders...

Would you care to elaborate on the tables that you have in your system and the relationships that you've defined?

[arjayefem]

The table that I am referring to is just for sizes. Available sizes (from my supplier) change all the time and I want to be able to track which ones are currently available. I do not carry inventory so I need to be able to see what sizes are available from my supplier and I will be using this information on my order tables, etc. But, you are correct in that I am tracking inventory with checkboxes, just not my inventory.

The "Sizes" form will only be used if a particular size becomes available or unavailable if it was previously available.

Hopefully this makes sense.

[bcooney]

Well, I'd have a child table of sizes available for each product ( a portal). Then you could add/remove sizes by adding/removing portal rows in this table.

Then, on an Order form, when you enter a ProductID in the Order Line Items table, I'd show a conditional value list of sizes available.

Edit: Instead of adding/removing sizes, have a flag field flag_IsAvailable, that you select and deselect. This way, you can see what sizes are offered, and update their availability. Only those that are available would be shown in the value list on an order.

Is a product available from more than one supplier? That is important to how you'd structure the relationship of the product and the sizes offered.

Edited April 5, 2009 by Guest

[arjayefem]

Thanks for the help. I think I understand what you are saying there, but I still need to know how to use these checkboxes for elsewhere in the database. Same deal...how do I check the box on a form and have it store in an associated table. Then when that information is recalled later, how to keep the checkbox checked according to the data.

[bcooney]

You should research using Lookups.

[Søren Dyhr]

Is a product available from more than one supplier? That is important to how you'd structure the relationship of the product and the sizes offered.

Indeed - But there are only 12 sizes (checkboxes) of each ... aren't they just a translation of a summary ... I do not quite get why it needs to be a lookup?

Since there is no storage, how is the availability announced?

Are seaches made on these checkboxes?

--sd

[arjayefem]

Indeed - But there are only 12 sizes (checkboxes) of each ... aren't they just a translation of a summary ... I do not quite get why it needs to be a lookup?

Since there is no storage, how is the availability announced?

Are seaches made on these checkboxes?

--sd

Yeah, this is way more complicated than it has to be. I'm not trying to move the earth...I just want to use checkboxes as data entry tools, have the data stored on a table, and later (maybe a month, maybe a year later) when I go back to look at the same form, I want to data to be recalled to said checkboxes (either to leave them alone if there still is inventory, or change them). Searches, i think will only be done on the table, and even then I can't think that I would even do a search at all...I haven't gotten that far.

Also, as I said above, this is not the only reason to use checkboxes in my database. Either way, I can't find how to use these things effectively and frankly, one line responses like above do nothing to help a person who is new to the filemaker program.

Sorry if I sound annoyed but think back to when you were first learning this stuff and how frustrating it was when things didn't work out. Thanks again.

[comment]

I am not sure that even now I understand what you are asking. A checkbox field is a field just like any other field. It stores whatever you put into it. The checkbox part is merely a method to input data, and to show (selectively!) what's inside the field.

[arjayefem]

I am not sure that even now I understand what you are asking. A checkbox field is a field just like any other field. It stores whatever you put into it. The checkbox part is merely a method to input data, and to show (selectively!) what's inside the field.

RingSizes_Form--has checkboxes for sizes Size0, Size3, ...Size13. Also, there is a field at the top of the form in which they enter the product number and up should pop the product description (ring name, i.e. Treasure, Timeless, peekaboo) and whether or not it is in stock (in the form of a single checkbox for each size

Data from that form is stored in RingSizes_Table in the form of boolean, straight yes or no. That is all that table is for is to track whether a particular item # is available in a particular size. 12 or so columns labeled Item #, Size0, Size2, size3, etc.

When I get the word that a particular item number is available in sizes xxxx, I want users to be able to call up RingSizes_Form, type in an Item#, have the description pop up underneath and the current sizes pop into their checkboxes. Then the user will be able to click the check boxes so that it matches what is available from the company. It doesn't interface with the web, it is nothing but a form that when you call an item number, you can see what sizes are available so you don't have to fish through the RingSizes_Table.

Hopefully that explains it a little better.

Thanks again for your help

[comment]

Let me see if I get this straight:

There is a table of Products, listing the products that CAN be ordered. In that table, you have "12 or so columns", one for each size, to indicate if a particular item is available in that size or not.

There is another table of Orders. In this table, you want to select a product, and get the CHECKBOXES (i.e. the value list that governs how the field LOOKS) to reflect the available sizes for that product - so that the user filling an order can select only from the sizes that are currently available.

Is that correct?

[arjayefem]

I am not sure that even now I understand what you are asking. A checkbox field is a field just like any other field. It stores whatever you put into it. The checkbox part is merely a method to input data, and to show (selectively!) what's inside the field.

After my last diatribe, I had an epiphany whilst driving and thinking about this post ^. I, of course, was making things WAY too hard!! My wife (VB, SQL, Java, etc programmer) and I (previously MS Access) are used to making a form and a corresponding table to store that data input on said form. When I got home, I switched from FORM view to TABLE view (which I had disabled by the way) and BLAM...there's my info. No wonder no one could understand what I needed...

Sorry to waste so much of your time on this. I'm sure I will be back with more stupid questions to confuse everyone. Until then, a million thanks for being patient with me.

[bcooney]

I think the block that you had is that in FM, you enter data directly into fields in the database. It's not a two-step process, where a user fills out a form and then a "submit" process sends the form data to the table. (although you could develop a system that does that).

However, I still feel your data model has weaknesses. A checkbox set is limiting. Do you deal with products that have different values for sizes? For example, tshirts are available in small, medium and large; but rings are available in Size0..Size13.

You also never answered my question as to whether a product is available from more than one supplier. Also, you'd want to maintain the sizes ordered with an order, not what was available at the time of order (and especially retain the size ordered if the size ordered becomes unavailable in the future).

[Søren Dyhr]

However, I still feel your data model has weaknesses.

Anticipations, usually goes to the relational structure as first stop ... while ignoring whether or not the choice of tool makes any sense at all. Hence Grantos expression "One Table Wonder" the tool as such might be selected by accident, while the selector wears an entirely different metaphor when approaching.

--sd