One to many relationship

Question

Looking for opinions on if the the one to many scenario in Sage 100 can be created with Custom Office: one item # to many different models for example. The goal is to be able to search a model # and see all the item numbers that are compatible with that model. An item can be used on several different models. For example, a customer calls says I need a new battery for my Toyota and in Sage we want the ability to search the Toyota model # and have sage give us all the batteries that are compatible with the Toyota; yet in the system that same battery can be used on many different type of cars/models. We want Sage to be able to hold that one to many relationship and be able to search on it.

David Speck · Answer

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, y ou can use Bill of Materials Inquiry/Maintenance to search for the model and view all of the batteries. Y ou 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.

Sage 100

One to many relationship

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING