How To Store Multiple Unique records against each Customer ShipToID?

For simplicity, let’s say we sell computers to our customers and then provide support which we charge a monthly support fee for.

Each customer can have over 600 offices (ShipToIDs) and for each ShipToID, they could have 15+ computers.

We need to be able to uniquely identfy which computers Customer A has, at each location, and be able to store unique data per machine such as its ID#, the model.

I have tried to record the following UD fields:
SHipTo_UD.ComputerID
ShipTo_UD.ComputerModel

(It’s more detailed that this but I’ll keep it simple!)

However, of course, I can only store one machine’s data per ship to ID.
If I try to store 2 machines, it overwrites machine 1’s data with the data for machine 2, as the primary key in the Ship To Table is company+custnum+shipToID

Can anyone help me with where to store the machines’ unique data?

I feel like I should know this but I’m drawing a blank!

Hoping to achieve a report which will displaysimilarly to this example:

i do not know why not using UD table and create a record for each MachineID, and have more control and visibility of all relevant data, however if you want to do it this way you need to add MachineID to your primary key and this is what you missing.

1 Like

Why not use serial numbers for the machine ID, or lot numbers, then a simple embedded dashboard will show you the detail

2 Likes

Don’t look at it as “who has what”, but rather “what is where”.

A UD table with the PartNum and “S/N” as the keys.

If you make the shipto as the key, it would be complicated to move equipment from one site to another.

Edit: “S/N” is in quotes to mean the Identifier you assign to it. Not a serial number from E10’s point of view.

2 Likes

SerialNubers are your friend they can be tied to a customer / shitpto I believe

3 Likes

Hi - thanks for the replies - appreciated

What I neglected to say is that we also provide support to the customer when the equipment is their own, and so I think it might be difficult to us the part records.

E.g. we can provide antivirus and software support whether or not they buy the computer from us. We have part numbers for the support services offered.

I put “S/N” in quotes to imply that it wasn’t the E10 S/N. The “PartNum” doesn’t have to be a Part from the E10 system either. And however you plan to uniquely identify these customer supplied resources that don’t have a S/N, make it consistant.

Just curious … You considering a field to indicate if the “item” is user based (i.e used by specific user or users: computer, printer scanner, seat of Office, etc…) vs site based (something used by the whole site: routers, firewall, network equipment, enterprise Anti-virus, etc…)?

1 Like

Thanks Calvin.

It will be used by the whole site each time, not user specific.

(The computers I’m referring to are tills in shops, so each location is a shop, and the machines/computers are the tills (aka cash registers). So a customer could have 600 shops, and 15

  • tills per shop.

Most times we supply the tills but some customers have their own hardware already and just want our software and support cover.

I know this is probably really simple but I’m having one of those days where my head just can’t process it clearly!

A UD table is the way to go. Have all the fields you mentioned above, with the first 5 columns of your sample spreadsheet, as keys 1 through 5.

Then you can search/report this data based on Key2 (CustNum) and Key3(ShipToID).

Where it will get tricky is how much data checking you want to include. Like what to do if someone enters an invalid CustNum.

Also, think about whether you want to store the CustNum (which users don’t ever see in E10), or the CustID (which is what users are used to). Downside of CustID, is that it can be changed, then your UD table records would no longer line-up.

Others here could tell you how to make it so the UD table form could have a search button to retrieve the CustNum and CustID, storing the Num in the Key, and the ID in a non-key field (like ShortChar01)

1 Like

I take back using the Cust and ShipTo as key fields.

Only the combinations of Keys must be unique. (Not all keys must be used.)

The following records are okay (DB wise):

ABC, 1, 1-001, X1254, Berry-Alpha
ABC, 1, 1-002, X1254, Berry-Alpha

But shouldn’t be allowed from your point of view, as that implies the Berry-Alpha with ID#: X1254, is in two locations.

That’s why the MachineID and MachineModel should be the only key fields

1 Like

@ckrusen as per my understanding, MachineID is like serial numbers, so same MachineID can not exist in two locations, i think what @KMenz need to do first is analysing your Business logic VS data then check what Eicor standard functionality can do.

1 Like

But two different kinds of machines could have the same “S/N”. If MachineID is truley unique, then that’s the only key required.

Doesn’t E10 combine P?N and S/N for uniqness?

In E10, do you actually make the ShipToID be prefixed with the CustNum?

There is no need to do that. As every ShipTo record has the CustNum stored with it.

Thinking outside the box here, but why not integrate with ITSM. Then you get all the ITIL goodness, including AD integration and Remote supporting straight from the application…It even has a web based customer portal…

@josecgomez SerialNuber? Is that like a Newbie? But you just don’t stop being one. :slight_smile:

1 Like

yes, and if you make it the only key, then you need to validate all other input, i.e. SiteID, CustID etc, while if you make it the forth Key as i suggested, this way he does not need to validate any other inputs

Thank You everyone

Hi - I appreciate that they don’t, but before I arrived at this business their ShipTo ID naming convention was CUSTID-001, CUSTID-002 for each location.

Thanks for all your comments it really helps.