Custom Company and Employment Records

Hello and welcome to our bi-weekly series, Ask the Professor, where we tackle a question from you, our viewers. Today’s question comes from Martin. Martin’s question is: I am new to NetSuite, but have ten years’ database systems experience. Is it possible in NetSuite, having entered multiple contact records associated with company records, to input and then relate start and stop dates of employment at various companies? In other words – input resume job history then search for and obtain overlapping employments of all contacts in the database in order to answer which individuals worked together at the same time at a previous company? I have been informed this may not be possible in NetSuite? If in fact, NetSuite has classic relational database characteristics shouldn’t this be rudimentary using basic joins? Martin, thank you for the questions. I think it would be easiest to tackle the last questions first, so that’s what I’ll do, and we’ll come back to the first ones in a moment. What you are asking for should be possible in NetSuite, however it is not as simple as an SQL join. This is mostly because when you are accessing the database, you are really accessing views or pseudo views, that go through an application and abstraction layer. You also don’t have access to perform SQL commands to access data, so there is not a Join Per Se. Because of this, thinking about what you want to do in traditional database terms is difficult. But using the correct methodology it shouldn’t be that hard at all. I want to point out, that I will only be covering this in brief here, because that is really all I can do in a short video. This could easily be made into an entire course, or at the least a chapter in a course. But we can cover an overview of how this would work. Now, let’s talk about the first questions you have, and more importantly how you would implement a solution. As an overview of how the solution I would build would work, I would create several custom record types that would hold my data, and would use a script to perform the actual calculation of who worked with who. I would also have that script write to another record type so that the data is persisted somewhere, because while getting the data on the fly would be great if you could use traditional database commands, in NetSuite you can’t. So let’s take a look at how this might work in practice. The first thing I would do is create three custom record types in NetSuite. One for Company, One for the Employee, and one for the Employment Dates. Now if you’re used to calling these tables, as they would be in a traditional database, think of them in that way. Just know that in NetSuite they are called record types, though I drew them similar to how you would see database schema. The company record would have the company name, description, address, and any other key data you wanted to track about the company. In the question there was a mention of using company records, but in NetSuite there is actually no such thing as company records. It is likely that what was being asked about were customer records, however I would not pollute those and would instead create a custom company record type. I would also use a custom employee record type, which I have titled Company Employee, though you could use the existing one that NetSuite provides. The reason I would use a custom one is because there may come a time when you want to look at overlap in employment for contractors, shareholders, business partners or other people who you do not want to have employee records in NetSuite. Using a separate record type helps prevent non-employees polluting the native employee record in case you ever want to do something like this. Many instances of this record however could be created automatically if you wanted by using a server side suite script and pulling data from the native employee record. The last of these record types would be the employment dates, which would have the company, the employee, and the dates the employee worked for that company. Records in NetSuite can be parents or children of other records, this is similar to a one to many relationship supported in a traditional database. In that example, the parent is the one, and the children are the many. So one parent can have many children, but a child can have only one parent. And here are the relationships I would setup between the record types. I would have a parent / child or one to many relationship between the company records and employment dates records. This allows you to relate a single company instance or record to multiple employment dates records. I would also have a parent / child or one to many relationship between the company employee records and the employment dates records. This allows you to relate one employee record to many different employment dates records. This makes sense when we think about the employment dates records. Each instance of the employment dates record will have a single company, single employee, and a start and end date. So, if we have 10 employees and each one had 5 companies they worked for we would have 10 employee records and 5 or more company records. The number of employment dates records is multiplicative, and for that example we would have 50 employment dates records. Of course, it’s not strictly multiplicative, but you get the idea. In case you’re wondering why I have one of these labeled One to Many and the other one labeled Many to One. This is for the sake of clarity. The Many label is on the side where the Many or Child Record exists. The One label is on the side where the One or Parent Record exists. The next piece I would create is a server side suite script that looks at the employment dates table for employment overlap. The data the script generates is going to feed into another record type that we will look at in a moment. But basically, the script will run once a day, probably at night, and will look for any new employment dates records. For each new record it finds it will then check against every other employment dates record to see if there is any overlap. Now truthfully, I would actually also put some filtering logic in so that the script is only looking at those records that have the same company, and have dates in the correct range. This would help eliminate a lot of work the script would have to do that there isn’t any point in doing. After the script had run against an employment dates record, there would be no reason to have it run against that record again, so I would also mark that record as having been reviewed or compared from. All of these records would always be eligible to be compared to though. When the script found an employee, that overlapped with another employee at the same company, it would write that data somewhere. The place I would have it write the date to is this last record type I would create titled Employment Overlap. The employment overlap records would contain the names of Employee 1 and Employee 2, the Company Name and the dates the overlap started and ended. To get to the data, that is to get to information about what employees overlapped with other employees, you would run a report or saved search against the Employment Overlap record type. Think of this as querying the database. As an alternate you could also have data written back to the Employee, or have a relationship setup between the employment overlap record type, and the company employee, or standard employee record types. So this pretty much covers the basic idea of how the solution would or could be setup, but let’s take a look at NetSuite to see how we would actually accomplish each of these tasks. Now for brevities sake I am not going to go through the entire setup, but we will go through how to get started. To create the record types we would go to Customization, Lists, Records & Fields, Record Types and Click New. Let’s go ahead and create the Company record type. We’ll give it a name of Company, and an ID of _company. We’ll also give it a description of “Company record, used to find employee / company overlap dates”. We have a whole bunch of options we can choose here in the header section, but for our purposes the defaults should work fine. Likewise there are a number of subtabs where we can set all kinds of parameters for the record type, but we will leave these alone for now as well. Right now we just want to save the record, and we will come back to the subtabs in a moment. As soon as we save the record, you’ll notice there are more subtabs available. It’s these extra subtabs that we are concerned with, specifically the Fields subtab, the Child Records subtab and the Parent Records subtab. Neither of these were visible when we were creating the record type. If we click New Field we are taken to a page where we can create a new field. It’s this page where we would define each of the fields for our record types. This is similar to defining columns in a database table. Based on our diagram earlier, we would want fields for the Company Name, Description, Address, and any other key data. The address would probably be broken into its subcomponents such as street, city, state, postal code, etc. Since we would be using the Company name in a parent/child relationship it would need to be a List/Record type. We can create a new list by choosing New in the List/Record dropdown menu. We could define the list and add values to it here in this popup. But I’m not going to create that list right now so I’ll go ahead and close this window. To make the parent child relationship work we would also have to click this Record is Parent checkbox which would become available after creating the list. I’m going to go ahead and click cancel to take us back to the previous page. Once all the fields have been created correctly these Child and Parent Records tabs will be filled in automatically as well. There is a lot more to creating custom record types than I can show in this video, and we still have to get to the SuiteScript part. But I do want to leave you with a resource that will help, and that’s the categorized help which you can get to by clicking on the Help link up here. If you take some time to read through the Custom Records section here, and this opened up automatically because of where we were in NetSuite when we clicked Help. You should get a good idea about what custom records are, and what their attributes are. I would also advise looking at this Custom Fields section as well since it will give you all you need to know about custom fields. If you just go to the help center from anywhere else, these are found under SuiteCloud then SuiteBuilder. I’ll go ahead and close the help for now, and bring us back to NetSuite, because there is one last piece we still need to talk about and that is the scripting piece. To do the scripting you will need to write the scripts in JavaScript, which NetSuite calls SuiteScript. SuiteScript is JavaScript, with a few extras, though most of the extras are just functions and methods. NetSuite runs JavaScript server side as well as client side, but the scripts you will want for this will be server side. You will want to download Eclipse, and the SuiteCloud IDE plugin for it, since this is the easiest way to upload the code. You can find out how to do this in either help or SuiteAnswers. Once you have the IDE setup, have written, tested, and uploaded your code you will then need to deploy it. That can be done by going to Customization, Scripting, Scripts and choosing New. From here you would pick the script you uploaded, though I will pick this first script file, just so we can get to the next screen and take a look. On this screen, you would most likely choose a Scheduled script, and from here you can select the schedule, as well as pass in any parameters or add any deployments that are necessary. You can find information about all of this in the help documentation, or in SuiteAnswers. Now, I know this seems like a lot, and it is, but it really isn’t nearly as difficult as it may seem. In fact, I think the most difficult part of this for any new administrator is going to be learning how the custom fields and records work, and learning how SuiteScript and the deployments work. Keep in mind though once you learn this knowledge it transfers to everything else you do in NetSuite. And I don’t just mean scripting and setting up custom records. There is so much of NetSuite that revolves around scripting and custom records, that the only way to ever really become a well-rounded and competent administrator is to learn how they work. Of course, that is not all you need to know to be efficient administering NetSuite, but there is a lot that those two tools will give you. I also want to say that this sounds like one of those projects that I think is actually really good to start with as you are learning the system. It seems like it could have a high value, and high visibility, but also not be so critical that if there is anything wrong with it, or it doesn’t work perfectly out of the gate, management would come screaming with torches and pitchforks. Martin, thanks for the question, and we hope this is a sufficient answer to set you up for success. If you have questions you would like us to answer please send them to ask@erpprofessor.com, or just let us know what you think. You can visit us on the web at www.erpprofessor.com, or connect with us on social media with the following links. Thanks for watching.