There isn't a natural custom data structure in Sage 100 which fits that scenario.  Anything I could suggest would be a nightmare to maintain the data.  You could do something like this though.

• UDT with a list of models, and a column for their battery size / type (assuming there is such a reliable categorization).
• Then have a field in Item Maintenance to list battery size / type.

Look in the UDT for the size / type, then search for items with that size / type.

Are you using Advanced or Premium? This would easier if the Sage 100 data is in SQL format. Custom view?

It's advanced.

Hopefully Sage will dump WindX and do client / server like everyone else. This would also solve the ODBC SQL syntax being lightweight.

If you have the Bill of Materials module, one route you could take would be to allow the use of phantom bills, then, assuming the battery part # will never exist as a standard bill, you could set up a phantom bill for the battery part # with a suffix to indicate it is a phantom bill or collection of model numbers and then add all of the models' part numbers as components.  You can then use Bill of Materials Inquiry/Maintenance to search for battery part number to view all of the associated models.  You could use the Bill of Materials Where-Used inquiry task to locate batteries the model is used in.

If your models were set up as bills with the batteries as components, you can use Bill of Materials Inquiry/Maintenance to search for the model and view all of the batteries.  You could use the Bill of Materials Where-Used Inquiry task to locate the models the battery is used in.

You could leverage the alias item number functionality and set up either a vendor or customer specifically for the purpose of using it with these aliases so they aren't normally automatically selected by Sage 100 during data entry, could call it something intuitive for this purpose.  Then on each battery part number, you would set up an alias for the model number and use the vendor/customer you created.  Then you can use the flashlight icon in Item Inquiry Maintenance to search your aliases where the item code matches the battery part number or search the alias number for a match to your model number.  An alternate way to use this would be to set up the battery part number as an alias on each model.  In either case, if you use a dedicated vendor/customer, you can filter it on that so you know you are only seeing aliases for mapping between the models and batteries which allows you to keep a description of the part on each alias.  If you set up the alias on the model item code, then the alias search list will drill down to model item code whereas if you set up the alias on the battery item code, then the alias search list will drill down to the battery item code.

A UDT is a viable route too but you have to make sure you create a key field that will able to contain a unique key for every record/combination you can possibly have and then you need a field for each piece of information you want to store separately. 

Aliases, bills, and UDTs can be imported via Visual Integrator so if you pick a method, you can compile all of data needed into a source file and import it.

If you want to play around with the bill or alias approach, make a copy of your production company and test the approaches in there in small sample sizes so you don't invest a ton of effort just to find you don't like it or don't think it will work for you.

Hi David, I really like the phantom bill idea and I'm going to try it,  Thank you so very much.