Proposal summary

This proposal is for retrieving item sales data from iQmetrix and storing it in a Netsuite custom record..

Requirement

We need to be able to sell through data.

By any date range we want By any dealer?

Can it fetch daily and add it and we can analyze all that data through a saved search?

We will use the 8 digit UID to filter different stores .

Take all the sell thru data and add it to the custom record, so everything is searchable.

In the Netsuite UI, we’ll create a saved search.
Customers for iQ metrix script execution will be listed in this saved search. We added a criteria filter “RQ COMPANY ID” is not empty to the saved search. We use the “RQ COMPANY ID” to identify the customers for script execution.
If a value exists in the “RQ COMPANY ID” field, this customer will be included in the saved search.
By adding filters to the search, users can add new customers to the search and remove existing customers from the search.
Each customer’s sales report data will be retrieved by their store. This will be achieved by API call.
And create custom records in Netsuite using the item sales data from API response.
Custom record creation logic is describe below:

1. Custom record to store the customer Detail

We will fetch each customer detail from the saved search, and create the first custom record in Netsuite. This record will be master of all child custom records.
This custom record will be named the “iQmetrix customer detail”.
The “iQmetrix customer detail” custom record included the following fields.

Field name	Field type	Description
Client name	List	To store the customer name. This will be the list field and list the customer name. Non editable field. Set this field as parent. Listed this record under the corresponding customer record.
RQ Company Id	Free form text	To store the customer RQ Company id. Non editable field. This value will be fetched from the “custentity_jj_btn_602_rq_company_id” field.
UID	Free form text	To store the customer RQ Company id. Non editable field. This value will be fetched from the “addr3” field in the customer’s address book record. This field is used to identify the store. This will be the array of store UID values.
Last script execution date	Date field	Wewill store the latest date value of the script execution.

2. The custom record for storing the customer’s store details.

We will use the customer’s UID values to identify the store value.
For creation of this custom record, we will call the GetStoreList API endpoint.
From the API response we identify the user’s UID from the VendorAccountNumber value. The VendorAccountNumber included the UID value.
If a customer record contains the multiple UID in the address book we will fetch all unique UID values and find the corresponding store details for that UID.
This custom record is named as “Customer store detail”.
This record will be a child of “iQmetrix customer detail”.

Field name	Field type	Description
iQmetrix client detail	List	To store the customer name. Identify the iQmetrix client detail. Listed all iQmetrix client detail records in this list.
Store Name	Free form text	To save the store name.
Store id	Free form text	To store the store id value.
UID	Free form text	To store the store’s UID value of the customer.
VendorAccountNumber	Free form text	To store the VendorAccountNumber value of the customer.

3. The custom record to store each customer each store item sales report between the specified date range.

This custom record name will be the “Item sales report”.
We will use the getInventory API endpoint for fetching inventory data for specific clients and their store.
We will add the client id, store id, start date and end date filter in the api filter.
This will return inventory sales data by specific date range defined by the user.
This will return ProductSalesReportData for each item.

Sample response for the sales report

<ProductSKU>CASPOA000389</ProductSKU>

<InvoicedBy>101: It’s Eid Inc. – Hillsdale</InvoicedBy>

<DistrictName>Northern California – It’s Eid Inc.</DistrictName>

<ChannelName>Dealer</ChannelName>

<Description>Tempered Glass Screen Protector</Description>

<Refund>false</Refund>

<CategoryPath> >> Cellular Accessories >> Screen Protectors</CategoryPath>

</Product Sales Report Data>

We will store these data in the “Item sales report” custom record.
Also included the customer name and store id in each “Item sales report” record.
This record will be a child of “Customer store detail”.

By analyzing Customer store detail users get an overview of the iQmetrix item sales.

For the “item sale report creation,” we will ignore the historical date range data.
Every day between 12.00 am and 1.00 am, the map/reduce script will run. Then, using the previous day’s item sale report data, create custom records in Netsuite.
A saved search can be used to analyze iQmetrix sales data from the custom record. The “sold on date” field will be added to the Item sales report custom record. This field aids in the identification of sales data based on the date value.

For custom record creation, the script will ignore historical iQmetrix sales data.
Based on the customer and their store value, the script will retrieve current day item sales data. Then, in Netsuite, create a custom record.
Users must use the saved search to analyze the custom record data.