SuiteQL: Budgets Machine

Introduction

Budgets are a bit confusing to get using SuiteQL. They are a good example of how NetSuite uses utility tables to store data that doesn’t fit into the main table. I will abbreviate the Budget table to only show three months of data for readability purposes.

Example

Let’s look at an image of a budget in NetSuite:

ACCOUNT	JAN 2022	FEB 2022	MAR 2022	…Other Months	TOTAL
10000 Cash	10,000.00	20,000.00	30,000.00	….	1,000,000
40000 Revenue	1,197,000.00	865,000.00	(Empty)	….	3,500,000

Note that ‘Mar 2022’ is an empty month, the other months are not. This will be important later.

There are two main hiccups when trying to get this data using SuiteQL.

First each budget record only contains one account. If you look at the internal id of the record in the UI, say with an id of 1112 and then query the Budget table, you will only get one account even if the UI shows multiple accounts.

When viewing a budget record, NetSuite shows you all the budgets that match the year, location, department, class. In this post, we will imagine getting all the budgets for a certain location which we will call “Location 1” and a certain year which we will call “FY 2022”. We will see the same information as shown in the UI.

The second issue with the data is that We can’t store the amount information in the main Budget table or even in a straightforward detail table.

This is because the columns are not fixed. For 2022, we have the months of 2022 but for 2023, we will have the months of 2023. This is different than the Transaction table where the columns on the TransactionLine table are fixed.

NetSuite uses the BudgetMachine table to store this information.

But first, let’s look at the Budget table.

Budgets

The Budgets table contains all the peripheral information about the budget.

Here are some columns in the Budgets table:

Column	Description	Type
year	Year of the Budget, but this points to a period, not and actual year	Integer (internal id)
category	Either “Budget” or “Forecast”	Integer (internal id)
location	Location of the budget	Integer (internal id)
department	Department of the budget	Integer (internal id)
class	Class of the budget	Integer (internal id)
account	Account of the budget	Integer (internal id)

BudgetMachine

This table represents the budget detail with just three columns:

Column	Description	Type
budget	Reference to the parent budget (which contains the account)	Integer (internal id)
amount	Amount of the period	Decimal
period	Period of the budget	Integer (internal id)

As you can see, the field for the month isn’t actually a month, rather a period.

Getting the data

Now that we know the tables, let’s get the data. We won’t be getting the data the way it’s displayed in NetSuite, rather we will get multiple rows each one with a different account/period combination. We will write SuiteScript to transform the data into the format NetSuite displays it.

SELECT
    BudgetsMachine.budget,
    BUILTIN.DF(Budgets.account) as account,
    BUILTIN.DF(Budgets.year) as year,
    BUILTIN.DF(Budgets.location) AS location,
    BudgetsMachine.amount,
    BUILTIN.DF(BudgetsMachine.period) AS period,
FROM
Budgets
 INNER JOIN BudgetsMachine ON Budgets.id = BudgetsMachine.budget
WHERE
    BUILTIN.DF(Budgets.location) = 'Location 1' AND
    BUILTIN.DF(Budgets.year) = 'FY 2022'

This returns all the budgets for Location 1 and FY 2022 and their amounts for the period:

budget	account	year	location	amount	period
1112	10000 Cash	2022	Location 1	10000	Jan 2022
1112	10000 Cash	2022	Location 1	20000	Feb 2022
1112	10000 Cash	2022	Location 1	30000	Mar 2022
1113	40000 Revenue	2022	Location 1	1197000	Jan 2022
1113	40000 Revenue	2022	Location 1	865000	Feb 2022
…18 more rows	…	…	…	…	…

18 more rows plus the 5 rows above equals 23 rows. But 2 accounts times 12 months equals 24 rows. Where is the missing row?

This is because Mar 2022 is empty for the 40000 Revenue account. Because there is no “Mar 2022” field, only a potential row in the BudgetMachine table, we don’t get a row for this account/period combination.

But if we want to display it properly we’ll need to add the other months. In this case we can use the months from the 10000 Cash account which is fully poputated but it may not be the case for all budgets that they have an account that is fully populated.

We can use the AccountingPeriod table to get the months for the year. I won’t go into detail regarding the AccountingPeriod table but most of the following query is self explanatory.

SELECT
  MonthPeriod.periodname as months
FROM
  AccountingPeriod AS YearPeriod
  INNER JOIN AccountingPeriod AS QuarterPeriod ON QuarterPeriod.parent = YearPeriod.id
  INNER JOIN AccountingPeriod AS MonthPeriod ON MonthPeriod.parent = QuarterPeriod.id
WHERE
  YearPeriod.periodname = 'FY 2022'

This returns the months for FY 2022:

Months
Jan 2022
Feb 2022
Mar 2022
Apr 2022
…

We now have all the data we need to write some SuiteScript to display the data in the same format as NetSuite.

SuiteScript function

The goal is to write a function that takes a location and a year and returns the data in the same format as NetSuite.

This will return a javascript array as follows:

[
    {
        'account': '10000 Cash',
        'Jan 2022': 10000,
        'Feb 2022': 20000,
        'Mar 2022': 30000,
        '...Other Months':'...'
    },
    {
        'account': '40000 Revenue',
        'Jan 2022': 1197000,
        'Feb 2022': 865000,
        'Mar 2022': 0,
        '... Other Months':'...'
    }
]

First let’s define the functions that will get the data.

function getBudgetData(location /* internal id */, year /* internal id of period */) {
    const theQuery = `
    SELECT
    BudgetsMachine.budget,
    BUILTIN.DF(Budgets.account) as account,
    BUILTIN.DF(Budgets.year) as year,
    BUILTIN.DF(Budgets.location) AS location,
    BudgetsMachine.amount,
    BUILTIN.DF(BudgetsMachine.period) AS period,
    FROM
    Budgets
        INNER JOIN BudgetsMachine ON Budgets.id = BudgetsMachine.budget
    WHERE
    Budgets.location = ${location} AND
    Budgets.year = ${year}
    `
    return query.runSuiteQL(theQuery).asMappedResults()
}

function getBudgetMonths(year /* internal id of period */) {
    const theQuery = `
    SELECT
    MonthPeriod.periodname as months
    FROM
    AccountingPeriod AS YearPeriod
        INNER JOIN AccountingPeriod AS QuarterPeriod ON QuarterPeriod.parent = YearPeriod.id
        INNER JOIN AccountingPeriod AS MonthPeriod ON MonthPeriod.parent = QuarterPeriod.id
    WHERE
    YearPeriod.id = ${year}
    `
    return query.runSuiteQL(theQuery).asMappedResults()
}

And now the function that will return the data in the format we want.

function getBudgetDisplayData(year, location){
    const budgetData = getBudgetData(location, year)
    const budgetMonths = getBudgetMonths(year)
    const uniqueAccounts = [...new Set(budgetData.map(item => item.account))] /* [10000 Cash, 40000 Revenue]*/
    const data = uniqueAccounts.map(accountname => {
        return {
            account: accountname,
            ...budgetMonths.reduce((acc, month) => {
                const amount = budgetData.find(line => line.account === accountname && line.period === month.months)?.amount || 0
                return {
                    ...acc,
                    [month.months]: amount
                }
            }, {})
        }

    })

    return data
}

Here is a link to a SuiteScript custom module that implements the above function

Conclusion

A lot of reverse engineering was done to get to this point and I’m sure there are better ways to do this but this is what I came up with. SuiteQL is not fully documented and a lot of writing queries is trial and error.

I nearly had to used saved searches to get the data but luckily I was able to avoid it.

Stay tuned for the next post where I’ll start discussing Bills of Materials and how to get the data using SuiteQL.