Best practice for inventory app containing very different items

[Scott_Nesbitt]

I am creating a new app that track the inventory of items that are quite different, and am wondering what would be the best way to organize the spreadsheet.  Different items would have different fields that are required.

For example, an LPG forklift would need a "Tank Included" Yes/No field, but an electric forklift would have a "Voltage" enum field.  I will have about a dozen equipment types initally, and it will surely expand.

I don't really want to have a separate Sheet for each equipment type, but if I just have one Inventory Sheet I would have to have lots of columns that are only used by one or two kinds of equipment.

Is there a third option?  Or should I just accept a very wide Sheet and conditionally show/hide based on need?

Thanks for any ideas.

[dbaum]

I think you're right that it's just plain complex no matter how you go about it.

In case you didn't already consider a hybrid: You could presumably have a generic parent table with common columns like name, description, quantity, and type and then type-specific child tables with unique columns.

The only other approach (also complex) that occurs to me to consider is to use a single table of entirely generic columns (e.g., EnumCol1, EnumCol2) and column properties like display name, valid if, etc., driven by SWITCH expressions that reference a type column.

[RedVox]

You could have specific fields for each equipment type and add a hide/show formula that looks for Equipment_Type enum = "Electric Forklift".  I do this for one app with clients that can either be Food clients or Drive Clients or both.  If Food is selected, eight more fields are shown in the form.  If Drive is selected, two different fields.  Although with 12 item types, this approach might not be best. 

Another approach, expanding on dbaum's suggestion, would be to have a series of generic columns in your main table, and a separate table of inventory type templates with corresponding generic columns of various types (eg text1, text2, decimal1, integer1, integer2, date1, date2).   A template for "Electric Forklift" could have columns that contain label values for each value you want to capture related to the forklift.  Then, on the main table form, conditional show/hide calculations and label values  could determine how many of the generic columns to show/hide and what to call each of the columns.   When a user creates a new record, an early question asks for the Inventory_Item_Type (which would be the label value for the template table.) Once the user selects the type of item, the form shows the appropriate number of columns with the labels from the corresponding template record.   

With this approach, you could easily add a new type of item to your inventory at any time simply by adding another record in the templates table.  

Cheers

[Scott_Nesbitt]

Thank you both for the responses.  I think I am going with the an Inventory table with every possible column, and an Equipment / template table that list the equipment types and the names of the column that are applicable.  I would then only show the Inventory columns applicable to that equipment type.

Thanks again.