Ask the Expert | NetSuite Financial Reporting in Excel with Solution 7 | Feb 2025

Transcript

Evie: All right, let's get started. Hi everyone, I'm Evie, the Customer Success Manager here at Solution 7. Welcome to our first Ask the Expert session of 2025. Hope you guys had a great holiday and sort of end of 2024, ready to get back to it now.

Our main focus this year is to get all of our existing customers moved over to the NetSuite 2 data source, and Simon today is going to kick off our session by giving us an update on the NetSuite 2 migration. After that, we will move on to the Q&A. You guys have submitted some questions, so we'll go through those first, but if you guys have any questions while we go through the session, please do throw them in the Q&A box in Zoom.

All right, Simon, what have you got today for us?

Simon: Hey, good morning everybody, or sorry, good afternoon everybody. Yeah, welcome to this year's kickoff for the Ask the Expert sessions. This session is going to be very similar to the way we closed out 2024. I wanted to give you an update on the NetSuite 2 migration process that the team has been working incredibly hard at.

We've made some really solid progress. We made really solid progress towards the back end of last year and early this year, although when you see the demo, you'll wonder what on earth we've been up to, but I'll save that nugget till I can actually show you the software.

Just to give you a reminder, I'm going to keep doing this reminder in every session just to give you an update as to where we are. We're working to three different plans: an engineering plan, a communication plan, and a migration plan, which, as I say, must have kicked off now, what, middle of 2024, possibly early 2024.

The engineering plan is built around 4.12, so building support for NetSuite 2 into version 4.12   that is complete. We're now looking at the migration of people's workbooks from the old legacy adapter through to NetSuite 2 in the 4.12 build. Anything that's highlighted in red is currently in progress, anything in black that's not crossed out is future work, and anything that's crossed out generally is completed.

So where are we with the engineering plan? We have the ability to migrate customers' workbooks. One problem with the way the solution was implemented is that it only worked on a single-sheet basis, so we've now moved from migrating a single sheet one at a time to actually doing all of the sheets in the workbook, and hopefully, with a fair wind, I'll be able to show you that as part of my demo today.

Once that's complete, we're then going to finish off adding all of the missing mappings and how to deal with missing columns. So where you've got columns that exist in NetSuite   internally, we call it a mixture of NetSuite 1 and Legacy   where you've got missing columns in the Legacy data source that don't exist in NetSuite 2, how we handle those moving forward. We've got some ideas as to how to deal with those.

Generally, what we're finding though is any missing columns, we report those back to NetSuite, and NetSuite actually adds them into the schema for us. So while we were worried that there would be missing data points in the NetSuite 2 data source, NetSuite are quite proactive at fixing those problems.

So that's what it's looking like from an engineering plan perspective. From a communication plan, still keep coming to these Ask the Expert webinars. Again, the plan is to identify customers in groups and to communicate out. Again, we're not quite ready to do that mass communication yet because you'll see if I go to the migration plan itself, we are currently coming to the end of implementing and testing the migration routines themselves.

Those migration routines are   I should have actually put this in the slide   for anyone with customizations. So not only do we have to, between us, get workbooks migrated to NetSuite 2, we also have to migrate any customer customizations, typically what we would call an adapter. You'll know if you have customized, or you'll know if you have a custom adapter, because you will have worked with one of the support team to build that, and it's that customer adapter that needs migrating.

We do have an initial pool that we're working with, and really that's anyone's NetSuite account who we currently have access to, so typically anyone that's raised a support ticket within the last month or so. We're going to reach out to you if we've got your credentials, or we have one of our support team that has access to your NetSuite instance, to actually do a migration and to get you moved across as early as possible before we have to do the wider communication.

Because after that, we are going to have to do a mass migration, and as you can imagine, with somewhere between 300 and 600 migrations to do, we've got all that communication work to do. We've got to get customers to add a support log to the NetSuite instance, we've got to run the routines, and then we've got to send out and install the customization itself, so that's quite a hefty task.

So we're going to, as I said, work with a relatively small pool of customers to begin with, and we've already started that process. I think we're on to our second or third from a customization perspective, and we're learning lessons as we go through that before we start the mass migration.

Just as a reminder, you can download version 4.12 from our website right now. You'll see here that it was last updated on the 22nd of January   is that, yeah, on the 22nd of January. So for anyone who doesn't know, we do update the website regularly with bug fixes, with versions for you to test. You don't always have to download and test them, but if you find a problem or you do want to run the latest version, keep an eye on the website probably every Wednesday or Thursday, and you may see a new build pop up. And as I say, the latest build is from the 22nd of January.

Right, let's go into what is probably going to be the least spectacular demo I've ever done, so let's go in and have a look at that. I'm going to repeat the demo that I did last time, where we actually migrated a customer list, but I'm going to show you how we've made it even less spectacular than before.

So here I am in Legacy. I'm going to insert a list of classes   only just activated, so Solution 7 is kind of still coming up to speed. There we go. So we've got a set of classes. Let's just save that into a known location, so just bear with me while I save it, and I'm just going to pop it on my desktop.

Let's now switch over to the NetSuite 2 instance. So as you can see here, all I've created is a Legacy data source and a NetSuite 2 data source. Again, when all of this functionality is fully complete and we have those migration routines, what we'll probably do is, for anyone that doesn't have customizations, we'll probably run a series of webinars and invite people to join just to go through this process together. I don't think we'll make it an Ask the Expert session; we'll try and keep it more as an interactive customer session just to focus on that, because I can imagine there's going to be lots of issues and questions about the migration.

How I'm doing it here is I've simply got two config files   a Legacy config file and a NetSuite 2 config file   and you'll see here that for my NetSuite 2 config file, and by the way, all I did here was actually just take my Legacy file, copy it, and then I've just changed the data provider. You'll see I've got lots of them, to NetSuite 2. You'll probably only have one or two data providers when you drop that down.

Okay, let's just restart Excel. Where's my activate button? Zoom's in the way   there we go. So as you can see, everything about NetSuite 2 is identical to NetSuite 1, and it's really important that we've really tried to keep the experience for each of them exactly the same. So from an end user's perspective, you will really see very little, if any, difference at all between the NetSuite Legacy experience and NetSuite 2.

Right, so let me load up that workbook. Here's that list of classes that we inserted. Now, on the previous demo, I showed you how to migrate through the List and Table Manager. Now, this was the source of the problem in terms of only being able to update a single sheet because the List and Table Manager only works on the current sheet.

So we've really simplified it, and I told you it's going to be very unexciting. We've given you a button, and all you have to do is load up your workbook. You'll probably get a prompt to say, "This workbook contains list tables that are not compatible with NetSuite 2. Would you like to migrate?" or telling you you need to migrate. So all you simply have to do is hit the migrate button, say yes, and we work our way through the entire workbook and give you a progress bar as to how we're getting on with that migration.

So you literally just have to sit there and wait while it works its way through. The first one is always the slowest. Once it's migrated the first, then the rest of them are fairly straightforward. Once you click on OK, we give you a summary sheet just to tell you what worked and what failed. So we think this is a better way of doing it than using the List and Table Manager Um, the List and Table Manager gave us a great foundation, but actually, we found that this is a much better way of doing it because we can now leave you with that like a mini audit trail of what worked and what failed, and if there were any problems between us, we can try and troubleshoot and get those lists and tables migrated across. So I did say it was very unspectacular because what was a series of screens and buttons is now literally just a button, and all you have to do is say yes. Once you’ve migrated, you’ve then got full access to the software in terms of going through the regular List and Table Manager, and you can see now that’s not showing it in red. We don’t have the migration warning or anything like that, so super easy, and now a great way forward from having to do those worksheets one at a time   you can now do them all in one go.

That’s my demo for today.

Awesome, that’s the first time I’ve seen that, that looks great.

Super, super simple.

Evie: Cool, all right then, so let’s go to the Q&A portion of today’s session. We have had a few questions sent in already around NetSuite 2, so we’ll tackle those first, and then we’ll go through some reporting questions.

So we had a number of people asking when will NetSuite.com be deprecated or removed?

First question.

Simon: Okay, first question   or the second question, Evie?

Evie: Oh, sorry, yeah, that would be the first question.

Simon: Okay, sorry. No, the first sorry, just to say to everyone, yes, this session is being recorded. Generally, that’s our first question, yeah.

So technically, there’s a difference between deprecated, not supported, and unavailable. NetSuite have already officially deprecated the Legacy data source. That doesn’t mean it’s unavailable, that doesn’t mean it’s not supported. They won’t support it from an enhancements perspective, but what they will do is, you know, if there’s a showstopper in there that impacts all customers on the Legacy data source, they’re not just going to suddenly pull the plug overnight. So it’s been deprecated. It will go unavailable at, I believe, the end of Q1 2026.

So we are working to a window from today of 12 months. We expect the 2026.1 NetSuite rollout will disable the Legacy data source. So when NetSuite say to you it’s deprecated, don’t worry too much at this stage because any sort of support or unsupported features we can manage through our implementation of Solution 7 and the way we use the ODBC data source.

The key date to remember is 2026.1   so Q1 2026. We are hoping, we are working to a plan of having everybody migrated in 2025, which will give us three months of contingency should we bump into any problems. And once we get going with the migration, we expect it to be fairly painless. We’re still kind of dipping our toe in the water from a customization perspective, and that’s where we envisage any pain. So yeah, as I say, the most important thing is we expect that all to be done this year, so don’t worry about the deprecated   it’s the end of life that’s the issue, it’s when NetSuite switch it off, and as I say, that’s not going to happen until 2026.1.

Evie: Great, thanks, Simon.

All right, so yes, this meeting is being recorded that’s normally my first question, I forgot to ask that, but that’s okay. Move on to another question regarding NetSuite 2.

So how do users check if they’re on Legacy or NetSuite 2, Simon?

Simon: Yeah, go into the config file. So as an organization, there isn’t really a differentiation between NetSuite 1 and NetSuite 2. If you’re a customer that implemented Solution 7   oh, when did we stop putting people on Legacy? It would have been early 2024, maybe the beginning of 2024. So for round numbers, let’s say if you started using Solution 7 in 2024, I believe you will already be on NetSuite 2.

The way to check is: go to the Solution 7 tab, hit the configure button here, and have a look at your data provider. If your data provider says NetSuite.com   that’s why we call it NetSuite 1, because the data source officially is called NetSuite.com   if you’re running NetSuite.com, you’re running the Legacy version, the Legacy data source. Otherwise, if you’re running the NetSuite2.com data source, you are on NetSuite 2.

Now, the most important thing to remember there is, you know, there are scenarios where you could have machines within your estate configured differently. You do not want to be in a position where some users are running NetSuite.com Legacy and some users are using NetSuite 2.

I’d also add to that as well   it would be super helpful if you want to try migrating to NetSuite 2 and you just want to have a go at it. You know, take lots of workbook backups and do the config file settings yourselves   please do go ahead and give it a go. It really is as simple as just copying the config file, changing the data provider over to NetSuite 2, re-entering the user’s credentials, and you’re good to go.

If you do migrate yourself and you feel it’s successful, please do drop us an email, because we are building up a list of customers who have migrated, and it will save us a fair amount of admin if we don’t have to reach back out to you to establish that we need to take you through the process. So yeah, please do let us know if you want to have a go at it yourselves and if it’s successful. If you do have a go and it’s unsuccessful, again, drop us an email, and I’m sure we can jump on a call and figure out what’s gone wrong.

Evie: Okay, so one last question on NetSuite 2 that I have is: what are the implications of moving to NetSuite 2? Will the users have to rebuild their workbooks?

Simon: Yeah, that’s the whole intention of why we’re being so cautious. In theory, all you’ll have to do is hit the migrate button and we will migrate the workbook. Under the covers, you save it, you have your NetSuite 2 version of your workbook, because ultimately, NetSuite 1 and NetSuite 2 versions won’t be compatible with each other. So you just load them up, hit the migrate button, save it into a new folder, and you’re good to go   that’s the theory.

Evie: Cool, yeah, and if it’s not as easy as that, guys, let us know. We can always jump on a call and help you through it.

Simon: Well, I was going to say, Evie, what’s your experience been so far?

Evie: Yeah, it’s been pretty smooth, actually. Customizations have gone over fairly well, and the migration tool’s been super helpful to migrate lists and tables over. A lot of people actually just haven’t been using lists and tables they’re only using formulas.

Yeah, so formula-based reports just formulas, guys will just migrate over on their own. They don’t need to be manually migrated like the lists do.

Simon: Yeah, and if none of what we’ve said makes any sense, sit tight and we’ll reach out to you anyway.

Evie: Sounds good. All right, so now let’s move on to some reporting-type questions. I’ve got one from Stephanie at Adah Health a question on currency reporting. If there are multiple subsidiaries, what is the best way to consolidate the monthly results so that each subsidiary’s local currency numbers are translated to a budget FX rate rather than an actual FX rate?

Simon: Oh, okay, that’s supported in our advanced balance functions. I’m not going to do a “Building Solution 7 Reports 101,” so I’m just going to dive straight into the function. But the APB in fact, all of the advanced balance functions support fairly advanced multicurrency. So you have the ability, through the advanced balances, to report any company in any other company’s currency.

So let’s have a go at USA no, let’s have a go at Mexico, because I know Mexico’s got some numbers in it. Let’s do a balance for all the four codes, and let’s run it for Jan 2023. I think we’ve got data in there for that. So this is going to return a balance for all of the four codes. I’m not   and as I say, I’m not building this out, I’m not building this out correctly, I’m just kind of throwing some formulas in. So obviously, you wouldn’t hard-code the values typically directly into the formula so let's let's say this was called uh income, and obviously, we build out the rest, so we should get an income figure in Mexican peso, which is taking your time to come back. Must be on our V oh, I'm on our VPN, there we go. So, we can see here that we've got an income balance of just under 5,000 Mexican peso. Now, let's play with that in different currencies. So, I'm going to, first of all, copy that across, and the benefit of hardcoding all the formulas is I can literally just copy the formula and start playing with it.

So, let's have a look at changing the currency. Go to the option, hit the lookup button, and I can change the subsidiary context. So, I can say I want to view my Mexican subsidiary in the context of Consolidated. So, I know for a fact that my Consolidated notional subsidiary is US dollars. So, we've now converted Mexican peso to USD.

Now, this is currently reporting in the actual currency as opposed to the budgeted currency. Now, I don't know if we've got I'm assuming we've got budget currency set up. So, let me show you how to do the budget currency. What you need to do is you need to go in, and you need to add another option, and these can be in any order. So, if we now go into option two, we can change what we call the result basis. Let me just make sure that is the right one because I haven't done this for a while transaction time, subsidiary content, accounting book yeah, the result basis.

And I can go in, and I can base the result rather than it being on the actual exchange rate oh wow, our next three instances are running slowly today. Whenever you see this, by the way, we're just literally waiting for next week to respond to us. So, here you can see we've got all the different currencies. You can report in the actual consolidation rate or Consolidated rate, which means that we will choose the exchange rate based on the account type. You can force it to the average rate, you can force it to the current rate, you can force it to the historic rate, and you can do the same for budgets.

And what I mean by “we choose the exchange rate for you,” typically what you're seeing there is that for balance sheet accounts, it would be the current rate, and for P&L accounts, I think it would either be the average or the historic, but it depends on how your accounts are configured.

So, if you want to do it in the budget currency, here I can just flick to “budget Consolidated rate.” You can see we've now got a second option in the formula. Click OK, and yeah, typical we get the same number, and that would be because the budget exchange rates are identical to the actual exchange rates, but that would flick the calculation over so this will be in the budgeted rate.

And if you want to then start consolidating those, you can either so again, let me grab that and if you want to start creating consolidations, you could, for example, change the main subsidiary to a consolidation subsidiary. And if I were to lose the subsidiary context and plug in a result basis here, this is actually going to create a consolidation across the entire org structure, and all of the conversions are going to be done at the budget rate.

And then, while that's calculating, let's also do selecting individual subsidiaries. So, you can also select individual subsidiaries. For example, we could do EU, Europe, Mexico let's do EUR, sorry Europe, Mexico, UK. Click OK, click OK again, and you can see that we've done an ad hoc aggregation of three different subsidiaries, and in this scenario, because the first one listed here is Europe, these balances will have been aggregated together and displayed in euros. And that's because it was the first of the subsidiaries listed in that list of subsidiaries.

There's lots of different ways of achieving the same goal, but the key thing is to use that result basis to change the fundamental way we calculate the intercompany currency amounts, and we switch over from the actual over to the budget rates.

Evie: Cool, thanks, Simon. It's funny, so I was talking to someone the other day about this similar concept where you're selecting multiple subsidiaries of different currencies, and I didn't know that it took the currency of the first subsidiary chosen.

Simon: Well, what you have, just to add to that, it's not the first one that you choose it's the first one that appears in this array. So, okay, I guess it is the one you choose because it comes back in that sequence. So, if you want to change the sequence and you wanted to say make it UK, you can you can physically edit this array and move UK to the beginning.

Let me also add to that as well that if you were to set the subsidiary context again, that will override that logic. So here, and the reason we choose the first one is because we don't have a context to work with, so we just pick the first one. If you do have a context to work with, we will work with the context that you've chosen.

So, here we've chosen three subsidiaries. Because I'm going to override the subsidiary context, let's make it Canada. We've now got UK, Europe, Mexico being presented together in the currency used for Canada so, Canadian dollars. So, lots of different ways of controlling how to show currencies.

Evie: Great, thank you.

All right, so while we're on formulas, I had a couple of questions about bringing in the sale of units rather than the actual received amount.

Simon: So, units units sold I'll show that in a couple of ways. So, let's do the same here, and let's use the Advanced Balance function to pull in an aggregated amount, and then I'll show you how to do it from a pivot table perspective.

So, here we're going to do consolidated. I'm doing it on my income try again I'm doing it on my income codes. Let's run it for Jan 2023, and let's have a look at the option, and we should find in there that we've got the ability I think it's another one of the result bases we've got the ability to switch over to, and I don't know why this screen's really slow all of a sudden the quantity field. So, let's click on OK, click on OK again, and now you can see we've sold from our income codes, we've sold 89,1 something don’t know what those things are but they are quantities of stuff attached to our income documents.

And obviously, when we pull the quantity fields through, that completely overrides any exchange rate calculation, which is why it's part of the result basis option because you're either working on financial data that's being converted, or quantity information where there's no conversion needed.

Let's also look at that from a pivot table perspective. So, if you want to pull in item amounts or quantities into a pivot table, I'll show you a flattened pivot table. Let's do posted transactions, let's do the same again so, let's do Consolidated, let's do Jan 2023 and from here, we now go into the choose column screen.

And you can see that if I scroll all the way down to the bottom and go to “More,” then we can grab the item record, and from there, we can grab the item name. So, where's the item name gone? Item name/stroke number so, let's throw that over here. In fact, let’s actually let me actually show you how to do groupings as well, and aggregation. So, if I now double-click on that, you can also see that I can apply a grouping to it. So, I can group showing the underlying detail, or I can group hiding the underlying detail. Let's do it by hiding the underlying detail, click OK oh, and we also need to pull through the quantity amount, don’t we? So, let's do that as well. So, where's quantity? Quantity, there we go. So, let's pull the quantity through as well and let's sum that.

And again, any delay at this point is us waiting for next week to return the data. There we go. So, you can see here these are all the different item codes, these are all the different quantities sold, this was the income generated, and because I told it that I wanted to hide the detail, you'll see that it's grouped all the data and given us a little plus icon, so we can actually do like a pseudo drill-down into the underlying document itself to see what those amounts look like.

So yeah, just to summarize, there's two ways of getting to quantity: you can do it either through the Advanced Balance using result basis, or you can do it through the pivot table choose column screen.

Evie: Awesome. All right, I’ve just found my questions. Okay, I had a couple of questions on the feel more like support questions a couple on slow files. So, if you guys experience any sort of lagging or slowness in your reports, please do reach out to support, and one of us can jump on and see if there’s something we can do to help there.

Simon: Yeah, there are just to say that if you are experiencing a slowdown, what can cause a slowdown is a combination of the size of the workbook, the volume of data that's being returned from NetSuite, and the complexity of the functions that you're calling. So, there are some efficiencies that you can put in place to do with parameter matching, which we can talk to you about on a call. But basically, you want your function calls to be consistent, and the more consistent they are, the fewer the amount of calls out to NetSuite we’ll make.

So, for example, if you're filtering by class for one formula and filtering by location in another formula, that would be two typically two hits on NetSuite. If you have a formula where you're doing a filter by class and then you say filter by location and put an asterisk in, that will actually be serviced at the same time as the class filter as the transaction as the location filter, sorry. So, you can put some tricks into your spreadsheet to match up parameters, which will actually help our engine run fewer queries against NetSuite.

So, that's one trick you can use. The other is to use formula locking so that if you've got a particularly large workbook, we don't attempt to recalculate the whole workbook. And the other is just to make sure you don't have Um, too large a workbook in the first place, and obviously data volumes in NetSuite can impact performance as well. We do have some very, very large NetSuite instances that we work with, but we can only operate as quickly as NetSuite can return the data to us. So, I hope that helps, and you know, if it would help, maybe we could run a session looking specifically in more detail at how to do some of that performance tweaking in one of these sessions. So, if that's something you'd like to see, you know, drop it in the Q&A or send an email to support, and maybe we can arrange that for a future session.

Evie: Great, thanks, Simon. Going to be helpful.

All right, I had a question on how to create a budget using Solution 7. I'm sure we've done a webinar on this already.

Simon: We have, yes. So, there's a historic webinar that I recorded where we go through doing an entire end-to-end budgeting cycle using Solution 7, Microsoft Teams, and SharePoint. Now, you don't have to use those technologies; at the end of the day, you can use the Solution 7 toolkit however you want to build and manage budgets. But what it does do is it walks through the fundamentals of creating a new budget category in NetSuite, building a template workbook, publishing the template workbook, mapping that workbook, and uploading the budgets back into NetSuite.

So, Evie, why don't we, as part of the follow-up, put out a link to that recording? Because it's a good sort of 30–40-minute recording. So, rather than going through it now, we'll send out a link, and customers can just go through that link and watch it through. And then, if there are any follow-up questions, maybe we can handle that through support or defer some of those to the next session that we run.

Evie: Sounds great, let's do it.

All right, so my next question is around showing item receipts by item.

Simon: Sure. So, that's another pivot table. If, based on the question, I'm showing the wrong thing, please do let us know. You have the ability in fact, no, it's covered by both functions and by the pivot table. Let's do the pivot table first.

You can insert a pivot table, and let me again choose my parameters. I'm going to do star of the account. What that's going to do is it's going to return both sides of the document, and we can figure out from there what we want to see in terms of the finished output.

Now, with the transaction pivot table, if you scroll down, you'll see there's a transaction type filter, and one of the transaction types is going to be item receipt. So, let's see if we can find that. Again, I don't understand why this bit's running so slowly there we go. Right, so item receipt is there. Click OK, and let's go to choose columns, and let's actually pull some item information through like we did before. So, let's pull through again the quantity, and let's pull through the item name.

So, that's going to be from the item object, and it's going to be the item name/number. Let's put that at the top. In fact, I'll set this up exactly how I did the previous pivot table. So, we'll hide the detail, click OK, and let's aggregate the quantity. So, we're going to sum the quantity.

Let me just explain how this works. Typically, numeric fields would be summed. Any other field can be counted, maxed, mined, or grouped. Now, what does that mean? Well, if you choose to group by something, it means that you get different groups of data in your output. Now, one of the limitations of the way we interact with NetSuite’s pivot table engine is that any grouping fields need to go to the top of the pivot table, and any aggregation calculations need to go to the end of the pivot table.

Now, I'd like to say that's a limitation that we've imposed it’s actually a limitation of the NetSuite Excel pivot table engine itself. So, any grouping fields will always need to go first, and any aggregation fields will need to go last.

We can then run that, and what we should see is probably lots of zeros because we're pulling both sides of the document through, and what we're going to have to do is figure out how to filter that document so we only see one side of it. And then you can decide, for the purpose of your report, which side of the document you want to see.

So, let's just wait for those results to come back. Oh, nothing. What's going on there? Consolidated star January 2023 item receipt why don't we have any data? Okay, let's open up the dataset. Let's go back further. I'm guessing we've got no data for January 2023, so let's go back in time. Go all the way back to the beginning, and let's run it through to January 2023 instead. Let's see what that does for us.

And it does seem that our NetSuite instance is running on a G-slow today. There we go perfect. Right, so let's open that up. There you can see we've got the item codes and numbers or names and numbers. We've got the quantity, we've got the amount, and as we had before, we can actually open those up. And here we can see the amounts.

It does look like we are only getting one side of the document through. I was expecting to see both sides of the posting come through, but yeah, it looks like we're only getting the 1200 codes come through, so that's good. So, we're only actually being able to produce a meaningful report. We don't have to further filter out each side of the document to produce meaningful numbers.

And again, that's a very simple way of being able to grab transactional data in this case, the item receipt and pull it through.

Now, I did say that there were two different ways of doing it. The other way would again be to go back to the advanced balance function. Let's do that. Again, I'm going to do it was 1200 was the code so let's just try doing exactly that. Let's do the 1200 code. Let's go back to the beginning of time, January 2023. And again, we go back to the option-value pairs, and one of the options that we can filter on is the transaction type. And so again, we can actually request a specific balance for a specific document type. And if we find item receipt okay, fingers crossed we should get a balance returned.

There we go. So, several different ways of achieving the same goal. I hope that answers the question.

Evie: Thanks, Simon.

All right, we have a question on adding vendor information to detail transactions.

Simon: Do you think that's to do with the pivot table, or do you think that's to do with drill-down?

Evie: I believe it's with a pivot, because the drill-down should already have the vendor on there. Well, let's just check. So, let's try drilling down and see what we get. So, let's just drill down to the transaction detail and see what comes back.

Simon: I believe it comes back in a column called “entity.” So, it depends on what side of the P&L you're looking at. If you're looking at the accounts receivable side, you should get customer information back. If you're looking at accounts payable, then you should get vendor information back.

The reason I also thought we could show the drill-down here is it's a good time to talk about customizing the drill-down. You can customize your drill-down. It's not something that an end customer can do. And as I was talking about the NetSuite migration, we can create what's called an adapter or a customization, and those adapters can contain an alternative definition for the drill-down. So, we can actually add columns for you to the drill-down so that you can see any custom fields or slightly different fields and data points as you drill into the detail.

Typical here my related entities are set to none, which is a shame. Oh, that'll be because it looks like we've pulled some journals through. But yeah, if these were accounts receivable documents, we would have the customer listed here.

From a pivot table perspective, again, let's do the same again in a pivot table. So, I quite like showing the flattened pivot table because it shows transactional data in a very sort of listy form. So, let's just pull that through. Oh, star January 2023. Let's do that wide range again. Let's do the whole of 2023 and see what we get January to December 2023.

And to choose the columns that you want to show in a pivot table, we go to choose columns, and you'll see if you scroll down more information, you have the ability to pull the customer, you have the ability to pull the vendor, or you can just simply use do we have entity still, or have we deprecated entity? Looks like I can't see entity.

So, we can go into the customer, we can find the customer name, company name that’ll do. Put it up there. Let's apply another grouping just for fun. So, I'm going to double-click, and I'm going to say show the detail. So, I'm going to group by the customer showing the detail.

And by the way, you can pull any of the customer details through onto this report. So, if you have a custom field that you want to report against maybe do a fancy grouping by again, talk to us. We can add it into your schema so that you So it's available to report from. I click on OK, click on OK, and there we have it. So here's our customer name, here are our groupings, and here are our totals.

Evie: Thanks, Simon. I did ask for the vendor, so can we show the vendor as well?

Simon: Oh, did I mishear you? I'm sorry.

Evie: That's okay.

Simon: So let's switch that over to vendor then. So I'm going to edit the table. I'm going to change it over to let's do five star, comma six star and let's go back into choose columns, lose the customer, scroll down, go to the vendor, and just as with the customer, we can pull the vendor name through. In fact, we also have company name, so let's pull company name through from the vendor table again. Let's apply. Let's reapply that grouping.

For anyone who doesn't know, this syntax is what's called an array. So you can do multiple selections using the array syntax, and this is simply starting with a curly brace and ending with a curly brace and providing a comma-separated list of values. So here, we're combining an array with wildcards so five star, six star essentially saying for the GL codes, give me all of the transactions for the accounts that begin with a five or a six. I think in this dataset, five is cost of sales, and six is expenses, so we should be on the right side of the P&L to pick up vendor information. Let's see what that pulls back.

Two for one, we get to do customs as well.

Evie: Absolutely. Just going to say, this is where it'll tell us you have no vendor information in NetSuite.

Simon: Yeah, let's have a look. This is one of the problems with demo data and demo data that we didn't create. This is NetSuite demo data, so yeah, there we go. So this is the vendor column here.

Evie: Nice.

Simon: Oh, and you were right no vendor information, but at least we've got some. Better than nothing.

Evie: Okay, right, we have got time for probably one more question. We've only got a few minutes left. So I have a question from Sudip, who's asking about “Is there a way to use operators to exclude from a list? For example, is it possible to use exclamation marks to do ‘doesn't equal location’ from a list of worldwide locations, to maybe exclude London?”

Simon: So, it's actually built into the list already. If I insert a list of locations, there's an “exclude location” field. So I can say I want to pull through a list of consolidated all locations, excluding and I know why the question said “Can we use an exclamation mark?” because that's a C syntax. So here, we should be able to say, right, let's pull them all through and let's exclude Boston. In fact, I'm going to do a wildcard to exclude Boston, so rather than having to select them, I could actually say I want to exclude star Boston star so anything with “Boston” in the name. Click OK, and here we'll have a list of locations, hopefully without Boston in it.

Now, while we're talking about excluding values, we do often get asked about how do you exclude a value from a formula. So, while I'm showing you how to exclude items in a list, let's also look very quickly at excluding balances. And that, it's far simpler than you can imagine.

So let's just build a formula quickly of all of the four codes from again our friend Jan 2023. Let's say we wanted to exclude a couple of GL codes from that it's really simple. There is no exclude baked into the function, but what you can do is just subtract one away from the other.

So let's say, for example, I wanted to subtract all of the 42 codes because, for some reason, I didn't want to see it in this particular journal report line. So here we're saying, include all of the balances starting four star minus all of the balances starting 42 star so effectively doing an exclude. So we get a different balance than if we were just pulling in all of the four codes.

And a top tip if you ever create a formula that looks like this, something that a lot of people don't know, which is super cool you can actually use the FX button to edit both sides of the formula just by clicking in the formula bar. So you can use the function arguments dialog to edit the left side of the minus or the right side of the minus.

If I click on the end, that's actually doing the whole thing.

It's actually doing I was thinking you can do the whole thing.

No, you can't.

Okay, so yeah, you can click on the left side or the right side, and can you see my parameters are changing in here? So it makes editing the functions really, really simple if you're embedding multiple functions into a single cell.

Top tip.

Evie: Let's do another webinar on all your top tips, Simon.

Simon: We have to we’ll have to write them all down first.

Evie: Awesome. All right, well, that takes us to the end of the session. Thank you to everyone for joining us today. Thank you for all your great questions, and thank you to Simon for answering those for us.

Simon: Yeah, and I always want to apologize for not getting through all of them. An hour really isn't enough, and we do get lots and lots and lots and lots of questions. So if there is a burning question that we weren't able to answer and you want some form of follow-up, please do email us through, and I'm sure we can jump on a call and help you separately. So yeah, apologies if we didn't get around to your question.

Evie: Yes, I was just about to say that. So guys, reach out to support. We should have a record of all the questions we weren't able to answer, so we'll be able to look at those as well and reach out to you to answer those.

Simon: When's the email going out, Evie?

Evie: Yes, it will be when Shannon and her team are able to get that out to everybody should be the next few days.

Simon: Perfect. And there'll be obviously, we'll be sending out the recording, we'll send out a link to the budgeting demo. We just need to give the team a few days to be able to top and tail the video and prepare it to send out.

Evie: I just had a message from Shannon actually it'll be four or five days.

Simon: There you go.

Evie: So yeah, thank you, Shannon. Monday at the latest. Hope you guys have a great weekend, and we'll see you at the next one.

Simon: Thanks, everybody.