How to Create Custom Records in NetSuite – Step by Step Instructions: Part II
Overview
In the first part of this blog we demonstrated how to create and setup custom records in NetSuite. In this second part you will learn how to manipulate custom records and use them in the context of transactions.
See part 1 of the blog series here: How to Create Custom Records in NetSuite – Step by Step Instructions: Part I
If you recall, in our business scenario we said that:
- The webinars will be held once per week and after each webinar the list of the attendees and the webinar's rate / fee will be imported into NetSuite.
- Based on this information, we will be able to bill our attendees.
- When we bill our attendee we will mark that attendee as billed, so that we do not double-bill our clients. Also, we will link the invoices that were created to our attendee records for reference and reporting purposes.
Step 1. Import Webinar Attendees
A. Create a Template
If you are not sure how to build the custom record CSV template, I recommend that you create several Attendee records and then expose their fields through NetSuite's Saved Searches.
- Assuming that you already created a few attendee records, go to:
Lists > Search > Saved Searches > New - Locate 'Webinar Attendee' Search Type.
- Click on the [Webinar Attendee] link.
- Once inside the Saved Search interface, click on the [Results] tab. Modify the default columns if needed, then click on [Preview] button.
- You should now see all the Attendee records that you created in your system.
- Click on the yellow-highlighted icon (csv export link) in the top left corner and save the 'csv' file on your computer.
- Open the saved file and edit it as required. Note: if you are adding the company values to the 'Attendee's Company' column, make sure that the names match exactly the company names you have created in NetSuite.
B. Import the CSV File
We now have created and edited our CSV file, and are ready to import it into NetSuite using the CSV Import tool.
- Go to Setup > Import / Export > Import CSV Records.
- On the next screen, please fill-in the values as outline below:
- Click on [Select] button and choose the file that you have created on your machine earlier.
- Click on [Next].
- On the next screen select 'Add' radio button and click [Next].
- Here, NetSuite will allow you to map fields from your CSV file to the fields on your Attendee record in the system.
- Note: the left pane displays the fields in your file, the middle pane helps you with the mapping and the right pane lists the fields on the Webinar Attendee record.
- Once you have completed the mapping, click [Next].
- One this final screen, enter the name for your Import Map and click on [Save & Run] while keeping your fingers crossed.
- On your next screen you should see something like this:
- Click on the [Import Job Status] link to see the status of your imports.
- If everything went as planned, you should be able to see something like this:
- Navigate to your Webinar:
Go to Lists > Custom > Webinar: WEB00001
- Check that your records were imported correctly.
Step 2. Bill Attendees
It's now time to bill the Attendees.
- Go to: Transactions > Sales > Create Invoices
- Let's create an Invoice for John Smith, who is one of the attendees we imported earlier.
- On the Invoice transaction I will select 'Jones & Bernstein Law Firm' in the 'Customer' field.
- Notice, I created a special form to use for these type of invoices (Webinar Invoice Form). This form was adjusted to show only the relevant fields and tabs.
- On the line level, we will add our item called 'Webinar Fee' (item type is 'Service Item’).
Before you can add this fee to your order, you will need to create your item under: Lists > Accounting > Items > New: Service Item.
- Important: Attendee field is a custom column field that you will need to create (Customization > List, Records & Fields > Transaction Column Fields > New).
This field will list Webinar Attendee records in the system, for this specific customer 'Jones & Bernstein Law Firm'.
See the images below for details on how to setup the attendee field:
- We need two more fields that will source the 'Webinar Name' and the 'Webinar Fee' when we select our attendee. Again, go to: Customization > List, Records & Fields > Transaction Column Fields > New
Create the following fields: - Webinar Name
- Webinar Fee
- Save the Invoice that you created earlier.
- Navigate to your Attendee record, by either click on the [John Smith] link on the line level of your transaction, or by going here: Lists > Custom > Webinar Attendee > List.
- Notice that the record has changed, the check-box 'Billed' is checked and the associated transaction appears in the 'Invoice' field. This functionality cannot be accomplished without scripting. If you are interested in the code I used to modify the Attendee Record after the Invoice for this Webinar was created, please contact me and I will be more than happy to share the code and the instructions with you.
Step 3. 'Webinar Attendees to Bill' Saved Search
Remember, in our business scenario we had two roles:
- Billing / AR Agent: bills attendees
- Project Manager: imports webinar & attendee-related data
In Step 1 we covered imports of Attendee records (activity performed by our project manager), while in Step 2 we billed one of our attendees (in our organization Billing / AR Agent will be creating Invoices).
Let us now help our Billing Agent by creating a Saved Search which will show the list of attendees pending billing.
- This Saved Search is simple to create:
Go to Lists > Saved Searches > New: Choose 'Webinar Attendee' search type
- Fill in the header section of the Saved Search as outlined below.
- In the 'Criteria' tab add 'Billed' custom field (check-box) to your criteria rows. Set its value to 'false' as you are looking for the Attendee records that have not been billed yet. See the example below.
- Modify the Results rows to display as follows:
- In the Available Filters expose your 'Webinar' field.
- [Save & Run] your Saved Search. You should see this screen:
- Notice, John Smith is not in this list as his record was marked as 'Billed' in Step 2.
- Note, through this Saved Search the billing agent will now know which attendees to bill.
- You can add this Saved Search to their dashboard (as a custom search portlet) so that they can see this information as soon as they log in.
- Click on the [Personalize] link in the top-right corner of your screen.
- Click on the [Custom Search] portlet icon in the gray slider.
- In the new portlet, position your mouse cursor over the top-right corner of the portlet and click on [Set Up].
- In the 'Search' field, select the Saved Search you created earlier and [Save] this window.
- That’s it! We’re finally are done!
In this blog you learned about:
- Custom Records and their application within NetSuite
- the basics for designing, building and configuring custom records
- the relationships between different custom records and NetSuite default records and transactions
- importing custom records
- customizing transaction forms to interact with custom records
- practical uses for saved searches on custom records
This comprehensive guide may not answer your specific business needs, however this methodology will help you design and setup almost any custom functionality in NetSuite. Good luck and happy designing!
See part 1 of the blog series here: How to Create Custom Records in NetSuite – Step by Step Instructions: Part I
Please do not hesitate to reach out should you have questions or need additional information about NetSuite.
Written by:
Ilija Budimir, Senior NetSuite Consultants
FMT Consultants
Posted by: Jakob Bechgaard