Ask the Expert | NetSuite Financial Reporting in Excel | Oct 2025

Transcript

Evie: All right, let's get started. Hi everyone, Evie here once again, welcoming you back to the fall, or autumn, edition of the Ask the Expert webinars. Today we're going to give you an important update on the Netsuite 2 migration, and then we're going to get back to our roots of Simon answering your reporting questions. If you have any questions during the presentation, please post your questions in the Q&A box, and we'll answer as many of those as we can during the Q&A portion of the session today. All right, over to you, Simon.

Simon: Thanks, Evie. Thanks, everyone. Welcome to, um, as Evie said, the, the fall Ask the Expert. Um, I can only apologize that we've not been able to run an Ask the Expert session sooner. Unfortunately, we have been flat out with the next week 2 migration. In fact, Evie, let's ask the question. Evie, how busy are you at the moment?

Evie: Unbelievably so.

Simon: All good, I hope, though.

Evie: Oh, yeah.

Simon: So, yeah. So, again, apologize for not, apologies for not being able to run an Ask the Expert over the last couple of months. Um, what has really triggered this session is an update that we've had from Netsuite regarding their timeline on the Netsuite 2, um, data source, and we really wanted, we, no, it really is, it's important enough that we needed to get this information out to you, and it kind of made sense to run an Ask the Expert at the same time. So you'll see that this session is called the next week legacy data source November test outage window. I will go into more detail in what I mean by that, but it is as important as it sounds.

Um, just as a recap on what we've been doing over the last few Ask the Experts. Um, as you'll remember, we've broken the migration exercise down into two phases. Phase one was updating all of the customers to version 412 of Solution 7. So that, in, that required a client install of the latest version. Phase two, which is what we're going through now, uh, covered the migration of customers from the legacy data source to the netsuite2.com data source. Both of those phases are outlined in detail on YouTube. So if you want to watch the previous webinars where I go through the process for both upgrading to 412 and migrating to netsuite2.com, please follow the link. Um, we'll also send out a link later, uh, should you want to, um, to, to watch it offline. So, um, yeah, please follow that link to the webinars and watch them if you want to catch up with what this process looks like and what you need to do. We will also be posting this webinar on the same link, so it will form part of the same playlist.

In terms of, um, the, the sort of, the volume, the sort of, the quantitative side of the migration, roughly 45 to 50% of customers are on the netsuite2.com data source, and I wanted to share this with you, because we have these deadlines looming and we don't want customers to be left behind. So please don't be left behind in the 50% that haven't yet migrated. Um, it really is important, and with the news that I'm going to share with you today, you're going to see the urgency that's behind moving off the legacy data source and getting yourselves over onto the new netsuite2.com data source.

Um, we have been notifying customers on a regular basis that this is going to happen. We've had some feedback that, um, people are now only just starting to learn that this migration needs to happen. Um, we've sent out several notifications, and I've just highlighted a few here. These are all emails that the team have been working on and trying to get the message out to customers. So, those notifications are going out. What I will say is, if you feel you are not getting those notifications, or those notifications are not going through to the right people, please let us know. Um, spam folders get in the way. Um, we might have the wrong details down for users that we need to send these notifications do to. So please, please, if you, if you're not receiving these notifications, please let us know, and we can get our systems updated.

NetSuite themselves are doing their best as well to get the information out. This is a confirmation dialogue that literally everyone I've spoken to doesn't remember seeing. And yet you are required to tick the box to say that you acknowledge that you've read and understand this notification. This is a notification that was inside Netsuite. So again, Netsuite are doing their best to try and let everybody know what they need to do and what the time frame looks like. NetSuite are also sending out notifications. So again, if you're not receiving notifications from Netsuite, again, please speak to your Netsuite administrator. Um, if they're not aware of the issue, speak to your IT administrator to find out where these emails are going. Um, it's really important that, that you stay informed about what the process looks like and how we can get you onto the netsuite2.com data source in a, in a timely manner.

You'll remember the timeline that I've shown in previous webinars. So, I've condensed it down now to just the 25–26 time frame. Here's the phase one, a pro, uh, process that we went through in early 2025. Here's the phase two that kicked off in June 2025. And again, as a reminder, these webinars, the webinars that kicked off these phases, are on the YouTube link that I showed you earlier.

Where this timeline has changed is we now have this new November test window that Netsuite have announced. Now, personally, I think Netsuite should be more assertive in the language they're using. It's not just a test window; it's an outage window. Netsuite are testing their internal processes to see how the switch-off process is going to work. What they're calling a test window is an outage window between the 4th and 5th of November. So that's in just over a month's time. Now, if that sounds serious, it is pretty serious. And if I'm honest with you, it's not great timing either. What it means is, from the 4th to the 5th of November, if you're running the, with the legacy data source, solution 7 will not work for those two days. I'll repeat that: from the 4th to the 5th of November, Solution 7 will not work if you connect to the legacy data source. So, if you don't want to be affected by this test window, you've got just over a month to migrate to netsuite2.com.

Now, I, I recognize it's not a simple task, and we're working as hard as we can to generate people's customizations and to send out as much information as we can give people about this process. But this is something that's being dictated to us by Netsuite. What we're expecting to happen is that when you try and use solution 7 on the 4th of November, you'll get an error message. I don't know what that error message is going to be, because it's being controlled by Netsuite, but you will see an error message. My suggestion is that you immediately raise a ticket with Netsuite and ask them what's going on, and ask them to reinstate your Netsuite access to the legacy data source. I recognize for a lot of customers this is probably happening at month end, so timing is not great, but this, again, this is something that's being dictated to us by Netsuite.

Just also say again, as I've repeated before, if you're already running against the netsuite2.com data source, you are completely unaffected by this process. You don't need to worry about this test window. It will not impact you.

How do you know if you are connecting to the legacy or the netsuite2.com data source? It's very simple. If you go into the system configuration dialogue within Solution 7, so you go in, so you fire up Excel, go to the solution 7 tab, and hit the configure button, you'll be presented with this screen. And if you drop down the data provider, there should be two options available. You can see here both of mine are 32 bit. Yours may be 32-bit, maybe 64-bit. That bit's not important. The important piece is whether you are currently selecting netsuite2.com or legacy. And it's not as simple a case of just switching from one to the other, because if you're running adapters, we'll need to get you your new adapter file. If you've got existing spreadsheets, those spreadsheets also need to be migrated and tested. And again, I cover all of this in detail in, I think, the phase 2 webinar that's again on the YouTube link that we sent out, I showed you earlier, and, and we'll send out as a follow-up. So yes, please do go into your configuration screen and check your data provider.

And, and just to confirm, um, when we're having support calls with customers, we're also getting them to check that on a regular basis, because we see a lot of customers that think, because they've upgraded from an old version to 412, which was the phase one part of this process, customers think that they've completed the migration. You haven't completed the migration unless you can confirm that you are connecting to netswuite2.com.

I've mentioned in a few webinars that we're running surveys. This process is not going so well, because we're just not getting the feedback. But please, please, if you are struggling with the migration, or if you've completed the migration, please tell us. It means we can cross you off, off the list. It means we don't need to keep sending you emails. Um, please jump onto the survey. It literally takes, like, one or two minutes, just to go, "Yes, I've done this. Yes, I've done that. Yes, I've done this. Yes, I can confirm that." There's a quick section where you can ask questions if you're stuck or if you're struggling. Let us know where you are in the process. It helps us, and it will help us to help you. As I say, we've literally probably only had maybe 15 responses to the survey. So, the more you can tell us about the, the process you're going through, and where you're getting stuck, or whether you've succeeded, the more you can feed that back to us via the survey, um, the easier it is for us to help you.

Again, the link's here, or we'll send a link out in the, um, with the, with the link to this web recording. Um, we will also, you'll also see that link in future migration emails where we're asking for an update. So plenty of, plenty of options there to, to jump onto the survey and, um, fill it out, and, and again it only takes a minute.

Finally, if you are running sweet tax, sorry, let me rephrase that. If you are running legacy tax, and you currently rely on Solution 7 to report your legacy tax fields, please send an, an email to Evie and the team at support@solution7.co.uk. There is a known issue that we're currently working on with Netsuite around legacy tax, and Netsuite are asking us to help them understand how many of their customers rely on legacy tax for reporting through Solution 7 and through the legacy data source. So, before you jump to the next2.com, because it, it also impacts your migration process, if you are running legacy tax with Solution 7, and you are using legacy tax fields within your Solution 7 reports, please, please drop us an email.

As I'm starting to wrap this section up, I can just leave you by saying, or leave it by saying, if you haven't migrated yet, start migrating now. I'm not even going to say please start migrating now. Start migrating now. We've got the November window coming up. We've got February looming. And I can't guarantee that there aren't going to be more outage windows between now and the end of the year where Netsuite are trying to do more testing or push people along. So the sooner you migrate, the sooner you can just put that problem to bed and move on.

And what I mean by migrating is, I don't just mean going in and reconfiguring your machines and just expecting it to work. Please try the migration. Please test your reports. Please have backups of your reports. You can't do enough testing. You can't do enough backups before you go live on the next2.com data source. So, please start that journey now. And again, everything, everything is covered in, you, on the YouTube videos. Um, again, come 2026.1, the legacy data source stops working. And there's just a reminder of the YouTube links.

Evie: Great. Thank you so much, Simon.

Simon: Have I missed anything, Evie?

I think we've covered everything. Uh, I know, I know you, I know you're at the coal face with this. So, is there anything that, that, that jumps out?

Evie: Um, I guess, could you go back to the configuration, uh, tab?

Simon: Absolutely. Yeah, absolutely.

Evie: It's really important to know. I've had a lot of, um, questions about this. Um, you can only see if you're connecting to Netsuite 2 in Solution 7 within the Solution 7 configuration. So the Netsuite 2 analytics connect page might say Netsuite 2, but unless your configuration is set to Netsuite 2 under data provider, as Simon is showing here, um, you, you can't tell any other way that you are using Netsuite 2 in solution 7. So make sure you check there.

Simon: And, and just to repeat what I said, Evie, just to confirm, when you're on a call with a customer, you are getting customers to open this screen and to show you.

Evie: Yes.

Simon: Okay.

Evie: Absolutely.

Simon: Yeah. So, it's really important that you can see on this screen that it's configured correctly. And as Evie says, the, the home, the inside, inside, inside the Sweet Analytics Connect configuration page within Netsuite, it lulls you into a false sense of security that you're connecting to Netsuite 2, because Netsuite are only giving you the details for Netsuite 2, because that's where they want you to be.

Evie: Yeah.

Simon: It's only when you go into this screen here that you know where you are connecting.

Evie: Yeah, absolutely.

Simon: Now, the question here, Evie, have we got any questions?

Evie: We do. So, for the Q&A, we do have some questions that you guys pre-submitted to us. Um, so we're going to answer as many of those as we can, and then we can dive into the Q&A box in Zoom and see if we've got any in there that we can answer. To start us off, Simon, I have a question.

Simon: Oh, go on then. I wonder what that could be.

Evie: Is this session being recorded?

Simon: Yeah, absolutely. This session is being recorded, and as I've said a couple of times, we will send the link out, and we will make it available as part of the migration series on YouTube.

Evie: Fantastic. All right. Okay. So, looking at the pre-submitted questions, I'm going to start with the Netsuite 2 related questions, and then we'll go into general reporting. Um, so we've got one question from TransStar. Um, they are waiting for their custom adapter to be updated for Netsuite 2. Once that is complete, they're asking, will they need to rebuild all of their reports for Netsuite 2, or will they simply need to update their configuration?

Simon: Do you want to start by answering that one?

Evie: Throw me in the hot seat.

Simon: Yeah.

Evie: Um, it depends, is a simple answer to a kind of complicated question. Your formulas should work. Uh, we can always review them with you if you need. Um, we have a migration tool, which has been very handy with this process, that will take care of your lists and tables if you have any in the reports. Um, so no, you don't need to manually rebuild your reports. Uh, but there may be some tweaking we need to do.

Simon: And I'll, and I'll add to that. There are a couple of exceptions. If we built you an adapter, gosh, how long ago, five, six years ago. So, we are calling them, doesn't really work, but we're calling them legacy adapters. Legacy adapters on the legacy data source. It could all get very confusing, this. If, if you have a function that effectively is named NSGA val, with your company name on the end, or something that it's referencing that implies that we built you a custom adapter in a, in a very old way, um, because that's, that was all we had available at the time. Those functions cannot be migrated automatically. We have no scripts. We have, um, hundreds of customers, um, that we're, we're trying to work through with the migration. So helping migrate those workbooks is just not possible. However, you should be able to migrate them by using search and replace, because you should be able to take parts of the formula and search and replace parts of those formulas out to reference the new advanced balances.

Now, if you do get stuck in that process, again, please contact support. That's why they're there. They're there to help. Um, so if, so, please try it first, but if you do get stuck, reach out to support, and we'll see what we can do to help you. Um, Evie, the first part of that question was about an, was about sending out adapters. Can you remind me what that first part of the question was?

Evie: Yeah, just asking when those will be sent out.

Simon: Okay, so yeah. So, the team are working their way through building adapters as we speak. I think we have successfully delivered about a hundred, and we've got about another hundred that are in progress. Um, so if you haven't received your new adapter yet, um, my suggestion is, if you're worried, reach out to us, and we'll, we'll, we'll try and, you know, prioritize things. We do need access to your Netsuite instance to be able to run, um, to be able to run some adapter code that, that can get the metadata from your Netsuite instance, so that those adapters can be built successfully. So that would really be the only holdup. Um, we have reached out to everybody that has an adapter and asked them who we should be talking to and how we get that process underway. Um, that was yet another one of the notifications that we sent out several months ago. So, if you haven't, if you have a custom adapter and you haven't been contacted, that's a problem. Let us know, because we need to know why you didn't receive the email, but we also need to get on with your adapter. If you've not received your adapter, um, I'd actually suggest maybe now, if you, if you're, if you're worried, drop us an email. Um, but we are working our way through them as quickly as possible. And the speed at which we get through them is actually the speed at which we're given access to a customer's Netsuite instance, so we can generate that metadata. Um, I, I hope that answers the question. We are doing our hardest to get through them.

Evie: Thanks, Simon. Could you show, how would the guys know, how would our customer base know if they have an adapter?

Simon: Oh, that's a good question. Uh, I'm not sure I'm configured correctly to show that, but let me have a quick look. Oh, yeah. Okay, great. Yeah, I am. Let me pull this over. So you'll remember in my side deck I talked about going into the configuration dialogue and look, naughty, okay, this is a, this is a dev version, but naughty Simon is still running the legacy data source. So come next month, even I'm going to have a problem. Um, but we're back, we flip backwards and forwards between legacy and, and next week 2 all the time. Um, how do I know if I've got a, an adapter? Go to the adapters tab. Am I right in thinking, Eevee, that if there is anything referenced in there, you have an, you have a custom adapter? So if this list contains anything, and any of them are ticked, you have a custom adapter.

Evie: Yeah, except for the really old ones. So, Simon, you have a One World adapter there. Back in the day, when we didn't have a One World Solution 7, we gave out an adapter, and those are still floating around somewhere.

Simon: Okay. Okay. So, so what I would say is, what I would say is, if we haven't contacted you about a custom adapter, and you have a custom adapter listed in here, and it's ticked, reach out to support. We'll jump on a call. We'll figure out what state you're in, and then we'll, we'll look to get you fixed and up and running again.

Evie: Sounds sensible.

Simon: While I'm in here, is there anything, is there anything else I can show while I'm in here? I'm not sure there is, really.

Evie: No, I think that's it. Yeah. Okay, great. Uh, so should we talk about reporting?.

Simon: Okay. 

Evie: Go through some reporting questions. Um, and then when we do the Q&A, um, from the Zoom Q&A box, if, if you guys have any burning questions about next week too, we can always get back to them. Um, all right. So, we've got, uh, one question about formulas. Um, is there a way to exclude GL accounts from within a formula?

Simon: Oh, okay. So, excluding GL accounts within a formula, is there a specific way of doing it? There is not. So I, I'm assuming this question has come about because, if I go into a list of, let's go into a list of accounts by number, for example, we have an include and an exclude option. Those form, those items are not repeated in the formulas. So if I go into, say, one of our balance functions, let's just pull something through very quickly. You'll notice, as I'm building this, that we don't have the option to exclude GL codes. Uh, I haven't looked at this data for a while. Let's assume we've got something in Jan 23, and have a look.

So you got a couple of ways of excluding values. Let's say, for example, you wanted to run a formula over two cells. Oh, can't grab it, there we go. Uh, I could pull in one value. I could change the value. I could change the formula, and say, let's exclude all the 4100s. No, there's no data for the 4100s. Let's try the 4200s. Oh, this is going to be a problem, isn't it? Uh, what about 4? Is that the same number? It is. This is not going so well. Let's try this shape and see if I can come up with something that gives me a value. Uh, 4,000, and I might have to just make a number up in a minute, 4,000, 4,010. You can see I don't know my data very well. Um, okay, let's pretend I'm excluding a number there, and it's 100. Um, we would just literally take one and subtract it from the other, and that would give us one minus the other. Now, there's nothing stopping you doing that in a, in a cell. Let me make my demo slightly more interesting, and let me pull up a report with some numbers in it, so that I can actually demonstrate this better. There we go. So, we've got, we've got 4,000. Oh, there we go. 4,000, 4,002. I knew there was a two in there somewhere. So, let's do that formula where I'm pulling through all the 4,000s but excluding the 4,002.

So, let me, let me set this back to how it was. So, let's pull through the 4,002 codes into a separate cell. What's going on? Oh, now Excel's playing up. Come on. Right. So, let's change that to 4,002. So what we're doing here is, we are taking one range of codes and excluding another range of codes. How can we do that in a single formula? Well, again, we just follow this principle. So if I want all of the four codes, and I want to exclude all of the 4,002 codes, all I simply have to do, oh, and copy and paste is gone as well, that's interesting. What's going on there? Can I paste that in? Nope. Let me do it this way. I might have to restart my Excel. It's starting to play up. So, let's say I wanted to exclude all the 4,002 codes. I just simply take one away from the other. Now, if I'm looking at it from a credit perspective, I would add the two together. So, because I'm looking at income here, and income is stored as a negative value, I would add the two together so that I would be netting out of all of our four codes, the 4,002. If I was looking at a debit entry, I could simply subtract one from the other. If you'll see, if I hit okay, that's the value of one minus the other.

Yeah. And again here doesn't match that number there, because I've actually subtracted the two of those. But that would be the correct balance, because we're looking at a negative credit entry. I hope that answers the question. And while we go for the next question, Evie, I'm just going to restart my Excel.

Evie: Go for it. That does answer the question. Thank you. Um, so the next one isn't actually a question. It's just a nice comment. Um, LSC loves solution 7. So, thank you very much. We also love solution 7.

It's very kind. Um, I can answer the next question about custom fields. Um, so, how is a new custom field made available in Solution 7? Um, so as you've probably gathered from the conversation so far, uh, we create an adapter for you to bring those into Solution 7. Um, so if you need to add a new field, new custom field, sorry, um, into Solution 7, please send us an email to support, and we can discuss with you what you need, where you need it, and what you're going to use it for. Uh, we have no way to make custom fields available, um, automatically at the moment. Um, I know we've had lots of questions about you guys creating your own adapters and adding fields to Solution 7 yourself. Unfortunately, we haven't been able to get that supported yet, but who knows? Hopefully in the future.

All right, Simon, how's your Excel doing?

Simon: It's not my Excel that's playing up. It's my, oh, there's my, my mouse pointer that was playing up. No. Yeah, something's gone strange. Yeah, just to add to what Evie was saying about, um, custom fields, um, the, the Netsuite database is huge, and what, one of the goals of Solution 7 was to try and protect you from the sheer size and volume of data points within Netsuite. Which means that, as Netsuite add new fields, we can make them available through a new release of the software. As you add custom fields, we can make them available through a custom adapter. Uh, we've been on a long journey with custom adapters, in that we used to have to write quite a lot of code to support customizations within Netsuite. We've now got that down to a fine art. The end game is hopefully that you will be able to go into Netsuite and go, I want to make this available in Solution 7, and that available in Solution 7, and this available in solution, Solution 7, and it will all just magically work. We are gradually getting towards that end game. We're just not quite there yet. But that is very much on our road map, because the more self-service we can make this, the more you can help yourselves. So yes, the, the two things. Number one, we're trying to protect you from just the sheer complexity of the database. And two, we're just, we're still on that journey to make it self-service.

Evie: Yeah, absolutely. Okay. So, next question is about, um, I think pivot tables. Um, so, this company have only been able to extract basic tables. Um, and they're asking about creating pretty dashboards. Are we able to do that?

Simon: Oh, that's interesting. So, let's talk about dashboards. We are, we specifically are not a dashboarding application. What we do is lean on the functionality provided in Excel to give you that presentation tier that you know how to use, and you know how to, um, you know how to build. Now, baked into that presentation within native Excel are charts and graphs, and I'm having mouse problems. Come back to life. Please come back to life. There we go. Um, so I can insert any graphing and charting feature from within Excel. pie graphs, pie charts, line graphs. And in fact, if I show you an example of a pie chart or a line graph we've inserted before, here's our standard demo, and here's a chart that was built. Now, you're going to need to understand how to build the chart. And you'll see here that if I, you'll see here that if I, um, go in and go, is it select data? There we go. You can see here that, um, you can see here that I've got my data series referencing formulas that are based on Solution 7 formulas, and it's that that's then driving the chart. So as I, as I refresh my workbook, you'll see that the chart will update as well.

Now, in addition to using the native charting functionality that's built into Excel, there's also dials and gauges that you can download from the Microsoft Store. So, from the Office Store, and if you jump onto the Office Store, there are, there are custom visualizations that will allow you to present the data in lots of very, very fancy ways. And you should be able to drive the data in those visualizations from the formulas that you build with Solution 7. So you shouldn't be restricted to just doing data dumps using the pivot table integration. You can absolutely use our formulas in combination with the pivot table functionality, and you can visualize that into a dashboard just using native Excel.

Evie: Awesome. Thanks, Simon.

All right. So, the next question is about the trial balance. Um, is there a way to easily create the TB without multiple pivot tables and manipulations?

Simon: That's an interesting question. So, building a trial balance via pivot tables. Um, it's not really how Solution 7 was designed to work. The, the pivot table functionality is really there for you to be able to pull transactional or operational data in. So, it's kind of what I would call this by that type reporting. So sales by customer, or, you know, SKUs by, uh, inventory by period, or, or something like that, or, or, or budget, budget, um, detail. To build a trial balance, now let me, let me throw something together relatively quickly, and I'm just going to do something very quick. But if I, if I literally insert a list, I don't want to do it by name, excuse me. If I insert a list of accounts by number, that almost forms, not really, but it almost forms the basis of a TB, because if I insert all GL codes by using an asterisk wildcard, I can pull my entire chart of accounts through.

Now, as I say, this isn't the full trial balance, but it's kind of close. So, there's nothing stopping you. And, in fact, we would recommend when you're building out your trial balance, by using your chart of accounts to build out a list, or if you want the software to, or if you want the report to run at a higher level, you can key those amounts in by hand. Or you could use a custom segment to drive the shape. And then, simply using an Excel formula. So here, I'm going to use one of our balance functions, and I'm going to pull through the values. And, as you can see, the, the, the neat thing about this is, we are literally going back to the source data. We're not having to go indirectly via a data dump. So we are literally pulling the data directly from Netsuite, and we can start to format it how we want to see it. And then the, the final benefit of pulling it directly from Netsuite, I, I'll show you while I'm waiting for the numbers to come back, but you also have the ability to then drill into the detail that sits behind those numbers. So as you investigate any issues that you might find in your trial balance, you can get to the underlying data.

And as you can see there, it's pulled it directly from Netsuite. I've not had to build any sort of interim data dumps or anything. I'm just pulling the report how I want to see it. And again, I apologize. It's not a, it's not a full TB, and my chart of accounts isn't quite in the right shape, but this is kind of a good starting point for how you would get to, um, a finished trial balance. I hope that answers the question.

Evie: Thanks, Simon. All right. So, also on the TB, um, we've got a question about pulling in debits and credits. Is there a way to do that?

Simon: Yeah, so, there is, absolutely. The way I would personally do it is I would take the formula. Hold on a second. Let me put some asterisks, let me put some dollars in here. So, I want to do one, two, three. I would jump to, back to the top. Right, let's format that and make it look nice. Right, so there's some numbers. You can see we've got a mixture of debits and credits. Let's take that and pull it into two columns.

Now, you've got a couple of ways of doing this. You can either use formatting, or you could use a formula. Let's do it using the formatting approach first. So, let's turn this into a debit column. So, if I format the cells and I go into custom, I don't know if you know how, um, the custom format works, but it's semicolon delimited, and this is just native Excel. It's semicolon delimited, and the formatting is a positive number, then a negative number, followed by zero, and then followed by a general format that doesn't really fit anything else that we've just put in. So, if we wanted to turn this into a debit column, we can have a format string for the positive element and nothing for the negative element, and all of our negatives disappear.

Let's say we wanted to do a credit column. I can do the same again using a custom format, but this time I'm going to pull that one as a, that one as a starting point. Uh, I'm going to put a semicolon. So blank, or the positive number, and then a format for the negative number. Now you see the default format I pulled there had a negative, so let's lose that, so it will actually render it as a positive number, and click on okay. And you can see there that I've split my debits and credits into two different columns.

Now, if you wanted to do that instead using a formula, let me just undo that, and I'll show you a formula way of doing it. So, let's edit the first cell. And, no, let me try that again. Let me edit the first cell. And we're going to say, if the balance is, and we're doing a debit column here, aren't we, we're going to say greater than or equal to zero, then return the balance; otherwise, return zero. And if we want to do the other side of the formula, I'm going to cheat and grab that, and we're going to say, if the balance is, I'm going to say less than or equal to, it's good enough, less than, less than or equal to, and drop it into that column. Did I get that the wrong way round? I did. That should be, spot the deliberate mistake, that should be greater than or equal to. And there you can see again, we split the columns out into negatives and positives. And then the only thing that would be left to do would be to correct the formatting so that everything is shown as a positive number.

So, how would we do that? Well, let's go in and take advantage of that cell formatting that I showed you earlier, and we can apply a custom formula where both the negative side and the positive side is being shown as positive number. Oh, we've got some zeros coming through. Let me just squash those. There we go. And again, we've split out the debits, the debits, the debits, and the credits. So, I hope that answers that question. It's a relatively simple process. Personally, I think the formatting approach works really well, because it means that, under the covers, you've still got all your numbers in your cells. So technically, you can add up the entire column with all the positives and negatives if you want to get a real balance. Um, if you only wanted to get the sum of the positive amounts, you can use, I think, the subtotal function, which will exclude anything that's hidden, or I just made that up. I might have just made that up. Um, but personally, from a pure formatting perspective, I like to use the, um, formatting approach. If you'd rather use the if approach, that works equally as well.

Now, I did want to show you one other thing while we're talking about showing debits and credits in a separate column, and that's to do with the way that the pivot table works. So, let's do the same again, but through the pivot table. So, I'm, I'm going to go into transactions posted, and I'm going to pull in all of the, I'm going to need to pull in a lot of codes here, aren't I? So let's do all of the four codes, followed by all of the five codes. And for those of you who don't know, this is called an array syntax. So this allows you to pull in multiple sets of values. This is actually an array syntax taking advantage of our wildcard character. Let's run that for Jan 2023, and click okay. I'm convinced our sandbox is getting slower and slower.

There we go. So, we have this sum of amount column over here. And it would be great if we could, again, in certain scenarios, show this as a positive or negative number. Because this is a pivot table, it's not easy to, to put a formula in here. But we've allowed for that by going into and editing the definition of the pivot table. So that's the screen I've just typed all the parameters in. And we've got this option here called choose columns. Now, just to show you, you can either click the choose columns button there, or, if you want to, you can right-click and go straight through to the choose column screen from the right-click menu. Once you're in the choose column screen, I don't know if anyone spotted up here before you go into the list of available columns, but you have the ability to enter a formula. And the way the formula is structured is, you type in names that are already included, so already pulled through. You can type those in in curly brackets.

So if I just do a straight amount column. Uh, okay. Yeah. And it's quite fussy about where things go. So let's move that up here. Oh, I might, let's make that a sum column as well, because that will actually work better. I can enter a straight formula. So there, we've just literally got the amount repeated.

If I go back in, I can go into my formula, and I can add my if statement. So I can do, if, open brackets, amount is, I'll get it the right way around this time, greater than or equal to zero, output the amount; otherwise, output zero. And let's add another column. And let's flip this the other way round. So that's going to be the amount where it's less than zero. Let's call this credits. Let's call this debits. Device debits. Click okay. Click, make sure that's set as a sum as well. So, can you see, as we work down, we've got individual field values, followed by some sums at the end. For those who don't know, any aggregation that you do within a pivot table, those formulas, those fields, sorry, have to be on the end. And if I finally click on okay, hopefully we should get a set of columns that are negative and a set of columns that are positive. And we can, again, use formatting to squash out the zeros where we don't want to see them coming through.

So, fundamentally two different ways of doing it. If you want to do it in a pivot table, use a Solution 7 formula embedded in the pivot table definition. If you want to do it using lists and tables, either use a formatting, or be super clever and, uh, sorry, either be super clever and use formatting, or do it using an if statement.

Evie: Also equally super clever.

Simon: Absolutely.

Evie: Awesome. That was great. Thank you very much. I know I get asked about this a lot. Um, so now we can reference this video. Excellent. Okay. So, uh, the, the next question, and maybe the last question, we have 10 minutes left, we'll see where we get to. Um, how can we pull things like, things like EBIT DAR or net income without having to pull the account level detail?

Simon: Yeah, this is a question that we, we get asked quite often. Um, I can understand that it's, that it can be seen as frustrating that you're having to manufacture formulas to pull through standard accounting values that you know you want to see again and again on reports. We have tried to build solution 7 as a toolkit, so that generally you can do anything you want with the GL that's in front of you. So we have specifically chosen not to target formulas like EBIT DAR, because all an EBIT DAR formula can do is give you EBIT DAR. But that's not to say you can't manufacture EBIT DAR.

Now, this goes back to, um, almost a spreadsheet that we created many years ago, which talks about advanced ways that you can aggregate values together. And I'm going to touch briefly on using wildcards and using custom segments. Now, I don't have the software configured quite right to show you the custom segments, but hopefully I can get the principle across, across to you.

When you're pulling a balance through, you'll have already seen that you can use wildcards. So, if you know the shape of your chart of accounts that will give you your EBIT DAR calculation, so, let's say, for example, we're doing, we want to include the sum of income, the sum of cost of sales, the sum of expenses, EBIT DAR. So, we've got earnings, which is income. We've got expenses. We've got cost of sales. We don't want, we, we won't want

So, the issue kicks in when we get into the sixes, because some of the expense categories we won't want to pull through. So, you might want to be more selective in what you pull from your expense section. So bank interest, for example, if you've got all those split out into its own GL code of, you know, other, other costs, and the same for other income, if you split those out, to avoid bank interest coming through, your chart of accounts should work well for you in terms of using wildcards. But let's pretend our EBIT DA was all the fours, all the fives, and all the sixes. I can simply use those wildcards, type in a formula, and there's my EBIT DAR value.

Now, if you want a slightly more, how can I best describe this, a slightly more controlled way of doing it, so I'm manufacturing an EBITR calculation here within an Excel spreadsheet, which means that it only exists in an Excel spreadsheet. If you work with custom segments, you can create a custom segment, and you can tag your custom segment against, um, individual GL codes. Now I'm going to quickly pull up a copy of Netsuite. Let me just log in, and let's see if I can show that quickly. So another way of doing it would be that, if you go into, if you go into Netswuite, when you're within Netswuite, it's very simple to create custom segments.

A custom segment, you could almost think of it in its simplest form. They can be more complicated. Think of them as like tags. You can see here we've set up tags called management account grouping, custom segment one, custom segment accounts, account plus class. You can call them whatever you want. To create a custom segment, all you do is hit the new button. Give it a name. Give it a name, and then decide what you want to attach it to. So, can I do that here? So, I want to attach it, in this scenario, to other record types, and I want to attach it to accounts.

Now that I've, now that I've created a custom segment that I can attach to accounts, I can go back in, and I can go to, um, my accounts page. So, if I go to lists, accounting, accounts, and by the way, I didn't save that intentionally, because I don't want to fill our test system up with, um, with brand new custom segments. So, I'll work with an existing one to show you this side of it. Go into an account. All of your custom segments will then appear on the account screen. And let's say, for example, we've got what we've called management account grouping. One of the custom segment values that you can create could be called EBIT DAR, and you could go in and individually tag each GL code that you want to form part of your EBIT DAR calculation. You can attach that to an EBIT DAR, which I haven't got. Let's just go with other. Let's pretend that says EBIT DAR and I just can't spell. Um, let's pretend that we've now gone through and attached that to our EBIT DAR.

By attaching those GL codes to a custom segment, I now have a grouping structure within Netsuite that I can then, that I can then lean on within Solution 7 to report back out. So, how would I do that back out in, in the Solution 7 part side of the world? First thing I would do is, I would send an email to support@solution7 to say, "Hey, I've created this new custom segment. Is there any chance I could add it into my Solution 7?" If you're using an existing custom segment and you're just updating the values that exist under the covers, so the individual items, you don't need to contact us. You only need to contact us if it's a brand new custom segment. But once you've done that, and you, we've shipped you the adapter, you can then use a formula called advanced balances. And within the advanced balances, if I go down here into our option value pairs, one of the options will be the ability to report by your custom segment.

And so, hopefully, if this is configured correctly, we will see my management account grouping option come through.

There we go. Uh, am I connected to the right, and I'm not connected to the right, um, I'm not connected to the right instance. That'll teach me to  Yeah. Anyway, had I have been configured correctly, it would have come through. I'm going to pick result basis. I could pick employee, employee, whatever. I could pick the segment that I wanted to pull through. And then, for the option value, I could hit the lookup button or insert a list, and pull through the individual custom segments themselves. So then, now, or, sorry, now I have the basis of being able to build a formula based on a predefined structure, which would be our Ebit DAR structure, which is a custom segment, and I can now pull that whenever I want through and into a Solution 7 report.

Probably not the best demo. My machine's playing up a little bit. Maybe in a future, after the expert, we'll actually go through that in detail, and, um, we'll give a, a, a proper rehearsed, um, example of how to pull that through. But you do use custom segments. They should be your best friend, and they're a great way of, of grouping values together and, um, passing them through and into your reports.

Evie: Thanks, Simon. That's awesome. All right, guys. Got two minutes to go. Um, I don't think I'm going to have any more time to answer any more questions. Um, but thank you so much for joining. Just as a reminder, um, if you're not sure, um, if you have an adapter for Netsuite 2, or if you haven't heard from us, you know, please reach out to our support box, and we can help out. We are currently, um, building the custom adapters as we speak, but it's taking a bit longer than we thought. So, sorry if there is any delays there. Um, if you've already successfully migrated to Netsuite 2 in Solution 7, please let us know either via the survey or just by emailing us. We would love to hear your feedback. Um, is there anything else that you want to talk about?

Simon: Uh, there are a lot of questions that have been submitted into the, um, into the Q&A. Um, so we will go through those after this call, and if anything jumps out, we'll either reach out to you, or we may even run another Q&A session sooner rather than later. So, we may end up having to do a follow-up session to this session, because it does feel like there's still a lot of unanswered questions. So, look, leave that with us, and we will try, and we will try and get back to you on those questions as, as quickly as we can, or, or run another session. Um, and, um, see how it goes.

Evie: Sounds good. All right, guys. Well, as a final reminder, the recording will be sent out to you in a couple of days, so watch out for that. And I hope you all have a good day.

Simon: Thank you everyone. See you next time.