Getting the Data into Dynamics GP (Video)

For transcript scroll down.

TRANSCRIPT

Here’s our agenda for this presentation. We’ll talk about starting the journey, right? So, the journey to an integration starts with what that integration is, defining it. What do we need to do? What do we need to accomplish? We’ll take that in and look at what’s already available in your system. So, you’ve got tools available to you now that may work very well for the integrations that you’d like to accomplish.

Next, we’ll look at what’s available in third-party products. So, what things are out there that maybe could streamline your integrations more, make them more functional, make them more useful, or just give you a little more power to do something really complex going to multiple sources, multiple destinations, running tasks, and scheduling and things. And then of course at the end, we’ll leave it open for questions and answers. So, we’ll give you some time to throw a couple of questions out if you’re curious about what any of these solutions look like. Of course, the slide deck will be available to you. I’ve got some blog posts in here that we won’t necessarily show on the screen but they’re there on the deck. So, you’ll be able to come back to this and really dive into some of these details after the presentation is over, too.

So, where do we start? At the source! Data pun completely intended. We’ve got some basic questions. What purpose does this solve? What are we trying to do? Where are we trying to go? Where is the data at now? So, we’ve got to answer these basically questions before we even start thinking about that integration. It doesn’t need to happen every day, every week as soon as there’s a change, right, a real-time trigger. Is it manual, can it be automated? Should it be automated? What if there’s existing data? What if we’ve got records already in our destination, right? So, maybe we’re integrating at the customers, and this customer is already there in GP. Do we wanna do updates there? Do you wanna throw an error? Do we wanna keep two data, two places that store that data in sync so that we’ll have to sync our destination and our source? A two-way type integration?

Do we have examples of that data? So, what’s that data really look like, right? Do we understand what the structure of our data is now, or do we just know that it’s in this other source and we need to get it into GP? And what’s that delivery for integration? So, how are we gonna process this? How do we wanna get data from one place to another? So, really think about, before you even start your integration, what you’re trying to accomplish and what your actual needs are. So, it’s really easy to start off and look at something that, CRM, for example. Maybe I’ve got customers in CRM, and I wanna do maintenance there, but I need to update GP whenever I maintain those in CRM.

So, you can start with something like that and just make sure you ask the questions all the way around it, right? So, how do the two-way updates work? How often does that happen? How do I handle duplicates because you can have duplicate customers in CRM, but you can’t in GP? So, think about those type of questions before we even start attacking in integration. And then where do we go? So, most or some of these solutions anyway can go to multiple places, right? So, we’re focusing primarily on GP here, but remember that these integrations may be two-way. So, maybe we’re syncing two sources which means we’re going to GP, but then we’re also pushing back to something else like CRM, Salesforce, Concur, or whatever you’re integrating with.

If this is a new process, maybe you don’t know the answers to all those questions yet. So, maybe you’re turning on a new module, and this integration is to get the data in there to start it. Or maybe you’re turning on a new module, and you need to constantly integrate something like data collection which is what GP calls the labor collection for manufacturing. Maybe you wanna turn that on, but you don’t want somebody to walk over to GP and type this in every day. So you’ve got another system that you wanna integrate that from.

If it is a manual entry process now or something you’re currently doing, what windows are being populated? That can be really helpful to help identify the tables and the fields and all the things that we actually need to integrate too. So, if you know how to enter the data into GP, you’re well on your path to building out an integration and knowing where that data should go. Knowing if those GP windows are modified or customized or maybe they’re even third-party windows, so there are a lot of ISPs for GP. I’ve tried a lot of that and people don’t even know that what they’re using is a third-party because it’s just integrated into GP. It looks like it’s part of GP, but it’s definitely worth knowing where this data is going and if it’s actually a GP window or not.

And we’ve got some more advanced questions. So, maybe it’s not just one source. Do you have multiple files, something like Concur Expenses, right? We get this all the time. I’ve got all these reports coming out of Concur. It’s got all my expenses. I need to push them all into GP probably as a general issue, right, to just capture the expenses. And do they actually map directly? So, maybe back to that customer example. I’ve got customers in CRM. I wanna integrate those into GP or sync those with GP, whatever we’re doing there.

There’s not really a direct map necessarily. I mentioned we can have duplicate customers in the CRM. Well, maybe we’re looking at the email address as the primary key, or we’re looking at the customer name. We need that customer ID, so we need to feel that GP is going to use to identify that. We might need a translation to do that. Same thing with contacts. So, customer contacts primarily are using the email address as their primary identifier. That’s not what GP will wind up using. So, if you need to sync that up somehow, we need to have a translation table or some method of translating that data in between.

You also need to think about what are importants required. So, if we’re looking at that GP window, we know what’s required, right? They’re either bold or red depending on your formatting. We can see which fields are required in GP. There may be fields that are required just from a business perspective. There are things that have to be populated every time because it is critical information for your business.

So we need to identify those when you’re thinking about an integration to figure out what’s very important, so we know that we actually map that, or maybe we throw back an error or do something if that piece of information is missing, which leads us to error notification in general. How do you wanna handle errors? And if this is something you’re running manually, an error on the screen probably works just fine. So, if you’re familiar with Integration Manager, you’ll see that certain records have failed.

SmartConnect gives you the same thing. If you’re running something manually, what if this is automated? Do we want an email? Do you wanna log that to the windows system event? Do you wanna create a record in the table? Do you wanna kick back the whole batch so somebody can manually review it and then try again? So, think about how you wanna handle errors. And then finally, how do we wanna reconcile between the two sources? So, if I’m just pushing data into GP, what do I do to make sure that I pushed in everything?

So, again, maybe expenses coming from an expense tracking system, how do I validate that all those expenses made it into GP? Do I just verify the batch total? That probably works in that case, but think about how you wanna reconcile that information to make sure that everything is in sync, or if you’re going one direction to make sure that everything made it into GP just fine.

So, here’s what you have now. We’ve got specific tools for doing single things, right? They’re not generally integration tools. They’re just tools that do fit one specific purpose, like the GL Copy/Paste. So, starting in, I think GP 2015, now we can copy and paste into the GL General Transaction Entry screen. We’ve also got budgeting. It’s Excel-based, so you could export to Excel, make all your manipulations, and import that budget back into GP. And then for Canadian Payroll, we’ve got the import/export utilities. So, we’ll touch on that as well.

You’ve also got some multipurpose tools. So, these are things that are not necessarily built for one specific module. It’s not just a Journal Entry import for example. It can go anywhere. So, the first of these is Table Import. You can also do direct SQL queries. So, using T-SQL or Transact-SQL. SQL, by the way, is a Structured Query Language, and that’s the platform that GP resides on. That’s where all your data stored is in SQL.

You’ve also got SQL Server Integration Services. You see that SSIS as the abbreviation frequently? That’s a platform that’s part of SQL that does some integration work. And you can do transformations. People use that a lot, building data warehouses, for example, point data out. You can also use that to push data in. We’ve got eConnect, that’s a GP tool that’s used…we’ll dive into exactly how that works. It can push data into GP using GP’s business logic. You’ve got Web Services. So, starting with eConnect and particularly with Web Services, we’re getting development-heavy there. So, developers, in general, love Web Services because it’s structured like an API. It’s something that’s very familiar to most developers to use.

So, those are options. We won’t dive too deep into the technical. You’re gonna see some code today. If you’re into that, get the slides. You can read more through the code. We’re not gonna go step by step through all the different things that are happening, but we’ll see it on the screen, so you understand the concepts. And if that’s your thing, then you can get the slides. You can look at the blog posts and dive in a little more…get in a little more depth into the technical side.

You may have Integration Manager. So, if you have the customization pack, you own Integration Manager. That may be most of you. It’s probably not all of you. So, we’ve listed it here, the things you have because a lot of people have that. If you don’t own that, we’ve got other tools that fit better for the same price or less. So, go and use Integration Manager if you have it because if you own it, you might as well take advantage of it. But if you don’t, then you need a third-party tool. We’ll look at some third-party tools that work well and cost less.

Finally, for the things you have now, you’ve got GP macros. So, we’ll look at how to record macros in GPs and out-of-the-box functionality, and then we’ll hack that with Word using Word’s Mail-merge function to really be able to do cool things. And if you wanna get extra technical, we can take that a step further and work with those in SQL as well.

So under our first tool, we’ve got the GL Copy/Paste. You just simply have this in Excel. We’ve got our description, our account, our debit, and credit. So, that description is actually your distribution reference down here. Copy this. You have to have that. It doesn’t have to be populated, but you have to have that column, and those columns have to be in that order. So, create your general ledger template, right, your Journal Entry template and just always use it in that same format. Don’t copy the headers, just copy the actual data, and then click that paste button. It’ll paste your journal as you write in.

That works really well if you’re getting…well, if you’re creating month-end entries in Excel anyway, you might as well just copy and paste them in the Journal Entries. If you’re getting downloads from something like Concur or your payroll system or something that’s already in Excel, you might as well just copy and paste that in. It’s very easy. You don’t have to build any kind of integration. You just have to structure your spreadsheet in that order.

Similarly, we’ve got Excel-based budgeting. So here, we can build budgets in GP, and there’s a nice wizard. Most people are already using this if you’re doing any kind of budgeting anyway. You export that budget to Excel. There’s the wizard to choose your amount so you can adjust things up by 5% from last year or however you wanna calculate your default budget amounts, export that through Excel. Do all your real budgeting work then in Excel so you can make adjustments, leave notes. You know, I’ve seen people pass one spreadsheet around to different departments to get everybody’s feedback. At the end, you just import that back in, and that goes into all your budget amounts in GP.

And then for Canadian clients, we’ve got the Canadian payroll import/export utilizes. So here, you can just work in Excel, and you’ve got a handful of different tools to fill the U.S. payroll files. You can import and export, so you can export pay codes, you can import pay codes, import job data if you’ve got jobs specific data that you’re working on or just span this directly in Excel.

Now here, I wanna take just a brief moment to talk about the dangers of integrations. So, any time you’re integrating something in, you’re making bulk data changes, okay? Now, there are several platforms where that’s relatively safe. Integration Manager, for example, will not bring anything into GP that can’t fit in that GP table. It won’t prevent you from creating the same Journal Entry 10,000 times if you accidentally have some extra rows in your table or didn’t have your group set up right.

So, there’s a risk in any integration. Always log in to test first. Always try things and test particularly the new things that you’re building. Once you fine-tune that integration and it’s working, just running that every week, you may not have to log in to test to try it every time. You’re gonna import some data using the same integration, but never build something out and try directly in live or directly in production.

I bring that up because this Table Import tool is scary. It’s very powerful. You can import this data directly into the tables. You have to know what you’re doing because there’s no data validation at all. This just brings the data right into GP, takes it from a spreadsheet, a CSV, or tab delimited file, and fills out the different columns in that table.

So, it won’t exceed the lengths, for example, the customer name there is a string, 64 characters wide. SQL itself can’t stick anything in that column wider than 64 characters, right? It’s already been built. You just can’t do it. But it won’t do the kind of data validation making sure the customers are in an appropriate class, making sure the address is valid, making sure the name is actually a name and not an address. You can really mess things up with the Table Import.

So, it’s there. Just make sure you know what you’re doing if you’re getting into that. And the same is true for direct SQL updates. So, you can use T-SQL, that’s Transact-SQL to make bulk data changes. Simply typing in insert, update, delete statements. You can pull from other sources, particularly if they’re already in SQL, and there are just minor updates that you can do. So, something like a step batch.

If you’ve ever called us to get a batch fixed, you’ve probably seen us log in to SQL, check some rows, delete something that got stuck, move it from one table to another. It’s great for that type of stuff. It can also be really good to pull, like I said, from another SQL source, so you’ve got another system. It’s on the same server. It’s pretty easy to write something that will copy that that data over. Also, in SQL, is the SSIS. So, that’s a platform to build out more complex integrations to transform data may be from one system and then put it in another, and it can also use flat files like CSV or tab delimited.

Now, I talked about eConnect a little bit being a Microsoft product. This does come with GP. eConnect is essentially a series of…but here’s the diagram for what it is. So basically, you connect through eConnect, or you talk to eConnect with XML. That’s where it does its integration. That can be through .NET programming. It can be through the Microsoft Messaging Queue or MSMQ. You’ve got different ways to call eConnect.

Then it’s got this basically runtime layer. You’ve got a set of stored procedures that are running on the SQL server that actually do the heavy lifting list, creates the records, or deletes the records, or updates the records in SQL. And then you’ve got Microsoft GP, of course. So, on the right-hand side, we call this out a little better. This is the process through which it goes. We create the variables, so we call out the different parameters or the things that we need to do. We’ve got this concept of a pre-stored procedure.

So, the eConnect nodes themselves can’t be modified. They’re actually encrypted, so you don’t see what Microsoft’s business logic is, what data validation, and all the different things that are happening, but to give us as a pre-stored procedure and a post-stored procedure. So, we can use this pre-stored procedure to manipulate our data and get it ready before it goes into GP. And that’s what this is calling out here. It runs that customized store procedure. Then I check the status from that.

So, I validate that the data coming back from that pre-stored procedure is still valid, still fits all the Microsoft business rules. Then I actually perform the integration of that record into GP. So, I’ve transformed my data. Now I’m running the process to push that into GP, and then I run this post-stored procedure. Same type of thing, it’s a customized stored procedure in SQL, and you can do some different translations, and you can now push that to maybe another source or destination that you need to go to, right?

So, I’ve integrated into GP. Now, I need to push this up to CRM, or I need to push this to some other application that I’m using, a business application or reporting application. Check if the status is successful and then ends. So, that’s what eConnect is doing. We’re gonna look at some code next. Don’t get scared. You don’t have to know the code, but if you’re interested, this is what one of those pre-stored procedures looks like.

Out-of-the-box, we’re not doing any manipulation with this, but we simply, to create an employee address, have all our variables here. These are the fields available in the employee address. The field types and there are also output parameters. So, we can accept all these parameters in, manipulate them, and then they get passed back as output parameters to the actual stored proc integrating into GP. After that’s run, we’ve got a post-proc that looks a lot like this, but they’re not output parameters.

So, you can call those through XML, through Web Services. The backend is all SQL stored procedures. You can also call them directly in SQL. So we can do something like this, and I’ll execute this, create employee, and I wanna create an admin person. It’s me. That’s not really my Social Security number, and we get these error outputs. So basically, we always output the error state and the error string. We select that when we’re done. If there was an error, we’ll see that message there, that error code in the message from the error.

So let’s take just a second to let you digest all that. Again, you don’t have to necessarily know all that code. You don’t ever have to use eConnect or Touch SQL. You can use something else. Just know it’s there because it’s a really handy tool particularly if you’re developing your own applications. So, if you do have developers on site already, they may very well want to use eConnect or use Web Services over eConnect to make those calls, but still manage all the stored procedures directly in SQL.

If you’re not doing that and you do own the customizations pack, then you’ve got Integration Manager. Integration Manager essentially runs things directly through GP macros, which we’ll touch on a little further. That’s not necessarily what it’s doing technically, but it’s very similar to that. What this does is you have to have GP open. You set what your integration is, so you map all the different fields. And then it actually goes through the interface and makes these updates. The benefit of that is that all the business logic in GP is followed because it’s actually going through the interface. Also, any customizations that you have are followed.

So, if you get prompts for error messages, we could disable those for customization, and Integration Manager would see that customization and ignore that error, or do something else with that data. So that’s a very powerful portion of Integration Manager that’s often ignored is that your customizations apply.

Then you’ve also got the ability to map to just about anything. So, if you open up the Integration Manager for the first time, you’ve got a lot of samples here. Those are very handy. You could build your bank rec, do customer imports, employee imports. There are a lot of things that are just out of the box, all you need to do is supply CSV file and make the appropriate mappings here. So, I select where I’m going, and then here’s where I’m pulling my source from. So, I’ve got a column in that CSV called GL TRX ONE. Oops, sorry, that’s a constant. But that’s where my source is coming from.

And next, we’ve got macros in GP. So, macros by themselves are actually really cool and underutilized, I think. And then I can go here tools, macro, record. Record some steps that gets saved as a macro file, and then I can play that back any time I need to. And you can even make those show up as shortcuts on your taskbar. So, instead of creating a shortcut for this window, I could create a shortcut for a macro that opens this window and then does a couple of things, maybe set some defaults so they won’t forget each time I open this, for example.

So by themselves, macros are actually very powerful and very handy. This is what the code behind the macro looks like. So you don’t have to know that code again just to record it and play it. Feel free to record the macro, play it back. These are just the steps that it actually records when you’re recording. And you can see it’s relatively plain text and easy to understand. TypeTo, the field as item number, and here’s the item number that I typed in. Move to field, and then I moved to my display option, click it. Simply it just means click. So I click my display option one next to the side ID, and then I typed into my location code warehouse.

So, it’s recording every interaction with that form or that window. So, essentially, if I click something, if I tab off, it doesn’t necessarily record the tab. It records that I moved to a different field. It won’t record your backspaces and things either, it just records what value’s in that field when you leave it. So, if I messed up item number and I had to retype it, it won’t record that and retype it each time. It’ll just give me the correct value each time.

So, really great functionality on its own. We can essentially hack this using Word. So, instead of Notepad, open this up on Word, and now I can Mail-merge it. So, if you use Mail-merge for writing company letters or anything already, you may be familiar with the process right here on Word. On the top, we’ve got start Mail-merge. It’s under the Mailings tab. And I like to use the wizard. So, we’ve got a blog post on this. I’ll show you some of how it works, but if you’re really are interested and wanna dive deeper, get the slides, and you can see the link.

Essentially, what we do is replace these fields with values on the spreadsheets. So, I’ve got a spreadsheet maybe with all these item sites that I wanna integrate. I do a Mail-merge. I replace my item number here with the mapped field on my spreadsheet. And then I save my new file, which is much bigger because I have all of this macro information for each file, for each line on that spreadsheet, and then I play that new macro file in GP. And you can literally watch with the Window open, the words get typed in, but it’s incredibly fast. And again, it’s all through the interface. So, you may be looking at some code here, but it’s relatively easy to find where I wanna map this, and build something that can integrate a lot of data into GP.

If you wanna get really technical with these, we can take this a step further and use that same code in your SQL SELECT statements. So here, well, the formatting is just a little bit cramped just so I can show the whole thing on the screen. This is what I use to migrate users from one server to another. Essentially, this queries my GP users, and then it fills out the macro text. So, here’s all the different things we’re going for the macro. And then I can add SQL character. So, here’s a variable that I’ve inserted, and I’ve set that equal to password exclamation point.

So, this builds out my macro file as the results from my SQL SELECT statement. I save that as a text file, run it, and it creates all my users and resets all the passwords to the default that I select in this script. So again, you don’t feel like you have to get technical on these things, but we have so much power here. If you’re comfortable getting into SQL, if you’re comfortable getting into the macro code, you’ve got a lot of power out of the box with GP to do integrations that don’t require any kind of third-party or integration tool.

That said, we’ll talk about some actual third-party integration tools next because there are some really great ones. We’re gonna look at SmartConnect. We’ll talk a little about Scribe, and then we’ll look at TitaniumGP Copy and Paste. So, these are the most used probably third-party integration tools for customization. There are others. And of course, you can do customizations. You can do just about anything if you wanna spend the development time on it.

So, whenever you’re thinking about these, when you start giving into something very complex that may need extra fields in GP that… you know, if you need extra fields that look at Extender first, of course, from eOne, who is the sponsor here today, they make a great product. You don’t need code to create those fields, but there are still times when you need to actually customize a window or create a whole customization solution. And if that’s the case, always talk to us about it first because you don’t wanna spend a ton of time for something that we could’ve solved with something like Extender, or could’ve solved with something like SmartConnect for relatively cheap. These customizations can be giant. You can spend all kinds of resources building something. If you think you’re going down that road, always reach out because we might have a simpler solution.

And oftentimes, that simpler solution will be SmartConnect. So, this is an eOne product. As I mentioned, they have a booth here today. Feel free to go talk to them. If you wanna dive into any more of the SmartConnect stuff or any other products in a little more detail or find more about what they do, SmartConnect is the no-code integration, migration, and automation platform. It is no code because you don’t need code. However, there are places where you can use code. So if you want to, if you want some extra power, we’ll look at how you can do that, too.

One big thing, I mentioned that not all these tools go right into GP only. SmartConnect is definitely a cross-platform from any source to any source type integration tool. So, these sources, for the most part, are all destinations as well. We’ve got CRM, Excel, InfoTab [SP, Extender, SQL databases, SmartList can even be a source, Access. I’ve even connected the file maker through the DBC.

If you can think about it, if you can get to it somehow through an API or you have a flat file somewhere, I have yet to find a source that I couldn’t pull into SmartConnect. So, it really does come from just about anywhere. Not only can it be just about any source, but this could be real-time. So, maybe we’ve got a web API that makes a call out to SmartConnect, so as soon as I do something in my custom web app, it fires up this map to do something in GP. Pretty easy to do.

You can write it manually. That could be change track. So, maybe I’ve got a table in CRM or a table in some custom app, and I wanna track changes there and do something with those. Or maybe I’ve got tables in GP. And when something changes in one of those tables, I wanna make sure that I pull in a flat file from a different source and reintegrate that into GP. The sky is the limit. If you can think of it, we can probably do it in SmartConnect. But it can also be scheduled.

So, I find a lot of times we just wanna update these once a day. Maybe I’ve got something that, oh, it’s another system, and I wanna make sure that at the end of every night, I pull in all of my new employee data and download it into GP so that my employees are all up-to-date the next morning. Easy to do. You can transform the data so we can do some entity lookups. You can, like we talked, maybe translate from an email address to a customer ID or something. You can do SQL lookups, just about anything that you can think of, like I said, you can do in SmartConnect.

Here’s essentially what it looks like. If you have SmartConnect or if you need it and you go out and buy it, one of the first things that you could look at are all the templates they have online. So, these are actually templates downloaded from SmartConnect going from CRM to GP or GP to CRM. Essentially, they’re already mapped out of the box, and you can make whatever changes you want to them to make them fit your individual needs.

If I drill down on the actual mapping, this is what the mapping looks like. And it may be a little small, but if you look, it really is drag and drop. I can drag my customer number over to my customer number field, and now my customer from CRM is mapped to my customer in GP. You can map all the different fields that are important to you. So maybe I wanna make sure I’ve got my city, my country, my facts, whatever you need, you just simply drag and drop until you’re ready to go, and then you save this.

If there are columns that you don’t have available on the source, you can do additional columns. So, when we click the additional columns button here, we get this additional window that lets us define new columns. So, these can be entity lookups. I can take something like my currency ID maybe which is just a text field or something at CRM, and find out what that actually is in GP so that my IDs match one for one. I can do a calculation. So maybe my shipping method, I need to just input the number of days, or I want to add two fields together to populate another field. Any kind of additional information that you want, you can calculate that as an additional column if it’s not available in your source.

And you can also do things like GP rolling column. So maybe I’m coming into sales documents. I’m creating new sales orders. I wanna pull the next number every time. So that’s not in my source, of course, because there are no sales document numbers in CRM or wherever you’re coming from, or they certainly wouldn’t match GP necessarily so I can just grab the next one. That’s a GP rolling column. I can create customer link columns too that don’t necessarily come from GP if you just want an incremental number.

And here we go to the code again. If you want some code to restrict this data, you’ve got this restrictions button up here. This can be C# or VB.NET. So, whichever you’re comfortable with that is globally set on the map, so the whole map would either be VB.NET or C#. But essentially, I can put certain restrictions here. So, I’m saying if phone one is empty or no, then don’t let that record map. So basically, stop this if I don’t have the phone one field set up. So, again, to run SmartConnect, you don’t need code. It is a no-code integration solution. But if you know how to code, you can do a lot of really cool powerful things.

So more things that we have in SmartConnect, we’ve got tasks. Now, we’ve got a task that run before the map, and we’ve got data check tasks, so we can actually validate the data before we do anything with this. And then we can run tasks before, or excuse me, after the document or after the map. So, the document is what SmartConnect calls the individual record. So, if I’ve got a spreadsheet, each row is a document, assuming they’re distinct rows.

The map itself is the whole thing. That would be my spreadsheet in that example. So if one document fails, I could send an email for example. Here, we’ve set up a task, email failure if it fails. That allows us to schedule this map to run or to trigger it to run from some other source, and it just goes along blindly in the background, nobody knows about unless there’s a problem in which case we get an email.

And then you’ve got some global variables that you can assign. So, I’ve done things where we maybe integrate from an Excel source, set some global variables, so we remember what the old filename was, and then archive it to an archive folder using those variables. The task, the things you can run in these tasks are more than just email. We can move files. You can run scripts. You can run code, copy files from one location to another, lots of really cool things we can do in those tasks. And then here’s what the schedule looks like.

So, when we wanna go and schedule a map, we can schedule this to run daily. In this example starting at 6 a.m., every one days, and then we’ve got this repeat function down here. So, we actually wanna repeat this every 20 minutes until 9 p.m. So, not only can your schedule say just do this at a certain time, you can repeat it every X minutes and end it either after a certain duration or end it at a certain time.

And that’s the more complex code if you wanted to go down this route. Here we’ve got a script, again, this is a demo on the eOne website so you could just paste this in and not touch it if you still don’t know how to code. Or you could write something very similar to this, and this simply checks for invalid GL accounts. So, we are making sure that the accounts that we’re importing are real accounts that they exist in our GL.

Here’s the pricing. You can buy this as a perpetual. You own it and then pay the maintenance on it annually, or you could buy a subscription. So, maybe you’ve got a big project. It’s going to last for six months. You wanna subscribe to this, use it while you need it, and then just stop paying the subscription. So it’s pay as you go, cancel any time. If it’s just a one-off thing, the subscription works great. But really, the perpetual cost is so low. Most people would just buy this. And again, if you don’t already own an Integration Manager, that customization pack is about 7,000. So, you’re paying a lot less for SmartConnect, and it’s a lot more powerful and a lot more intuitive.

When you buy it, you get the SmartConnect data integration solution, SmartConnect itself. There’s a cool add-in for Excel. Actually, it’s a button in Excel that you can use to run a map directly from Excel. You’ve got the web client, so you don’t actually have to install the app. You can access it from the web, and you get three instances of SmartConnect. So, not the client but the SmartConnect server.

You get all the connectors, access for limited users and all the templates again are available on the eOne website. So, their templates are extremely powerful, and they can get you pointed in the right direction for almost anything. And, of course, you’ve got to ask if you’ve got something that you just can’t get to work. In SmartConnect, we can connect and help you get that all sorted out.

So, look at the different tools that we’ve got here on installation security, mapping. We’ll basically just compare them all and how they line up. So, we’ve got Integration Manager. It’s pretty easy to use. The security is from GP. So, as long as you have security of the GP Windows, you’ll have security to that map or to map of those fields. You really can’t do much with the mapping. It’s drag and drop. You can do some scripts, but it’s really not very functional. They can be manual or scheduled. You do get an arrow log file, and it’s very slow.

So, to import 10,000 customers would take about a little over four and a half hours, plus it’s $7,100, and it’s a little clunky. SmartConnect is very easy to use. It will take a minute and a half to import those 10,000 customers, and it’s cheaper, and you can use a subscription. I also mentioned Scribe a little bit earlier. So, Scribe is somewhat similar to SmartConnect. It’s really developer-centric. So, developers a lot of times like it, because you’re writing code. You’re doing a lot to manipulate the data. It’s really difficult to use if you’re not a developer, but it does have a lot of power.

And then the last third-party that we talked about is TitaniumGp Copy and Paste. So again, this is less of an integration tool like Integration Manager, SmartConnector, Scribe, but it does more than just one window, unlike the GL Copy/Paste. So, TitaniumGP has paste windows and many GP windows, and they look a lot like this. So, copy the scrolling section, paste it. They’ve got something called elastic windows where you can add some fields and things to windows as well. But essentially, you copy the data you want from Excel, go to the additional menu on the window, hit paste, and it populates to that transaction with the data from Excel.

The nice thing about this compared to something like the GL paste are the fields are a little less stringent. So, I don’t necessarily have to have all the same fields like I do in the GL paste. In this case, I just have the item number and quantity. That’s all I really need. I can accept the default unit to measure and the price for my pricing as I set it up in GP. So there’s really no need to structure my spreadsheet in some fancy way.

So, a quick recap of these different tools. We’ve got the specific tools like that GL Copy/Paste, Excel Budgeting, Canadian Payroll, those multipurpose tools. So again, we looked at some of the more technical ones. If you’re developing something, you might look into eConnect, Web Services, SSIS, Table Import, etc. If you’re just real savvy, you might look into some Mail-merge macros, and learning how to hack those macros a little bit with Word. And if you’re really going all out customizations, then we’re talking about a Dexterity customization.

If you’re not looking down that road and you’re looking for an integration tool that’s easy to use, you’ve got Integration Manager. You may already own it. If not, SmartConnect is much more powerful and very, very user-friendly in comparison to Integration Manager. If you are doing the development work, you’ve got Scribe available out there to you, again, developer-centric but still a pretty good product. And then Titanium GP Copy and Paste if you just wanna do that copy and paste functionality in more windows than the GL Journal Entry window.

So, thank you for attending today, and I’ll leave it open for questions if anybody has any questions about integrations or any of the solutions we looked at.