1 2 3 Previous Next 34 Replies Latest reply on Mar 25, 2013 11:30 AM by disabled_JohnWolff

    MoneyWorks and FileMaker Integration

    wrangler

      Hello,

       

      I'm following up an earlier thread that started with a question about passing data between FileMaker and MYOB (https://fmdev.filemaker.com/thread/64376).

       

      In the course of that discussion I've reached the conclusion that MoneyWorks is the best accounting software to integrate with a FileMaker solution for small business. I think the reasons for that conclusion are probably apparent in the earlier discussion but I'm happy to add them here if needed.

       

      I still have questions though, and one of them is prompted by one of Lemmtech's posts in the earlier thread where he wrote:

       

      "There are many issues beyond just getting the data out of fm if you wish to integrate with an accounting system. I create a batch log process to establish an audit trail between both systems and other things to increase the integrity of the data transfer." He also cautions that integration of Filemaker with any accounting package should be approached with great caution, and done in sync with somebody who understands both accounting practice and the specific accounting package being used.

       

      This is clearly good advice, and as a stranger to accountancy I'm taking it very much to heart.

       

      But I'm wondering if the dangers are mitigated in any regard if the accounting software is MoneyWorks. As I read their product information, MoneyWorks was built specifically to enable integration with applications like FileMaker, and itself logs everything it handles with complete audit trails. If I use MoneyWorks and consult with an accountant, am I more likely to succeed in creating a robust accounting back end for my FileMaker solution? What pitfalls might I encounter on the way?

       

      Many thanks in advance.

       

      Regards,

       

      John

        • 1. Re: MoneyWorks and FileMaker Integration
          Lemmtech

          Hi John,

           

          I'll be the first to chime in again on your new thread. I don't know MoneyWorks so I can't say how difficult or not using it with FileMaker will be perhaps the can put you in touch with a consultant who has integrated the two systems before. It good to hear that they have audit trails on their side but you also will need them on the FileMaker side as well. The things you need to think about in FileMaker are:

           

          1] What data will you need in FileMaker to "match up against" the data in MoneyWorks. How does this data get into FM? Do users key it in or is pulled out of MW.

           

          2] How are you going to track what data has ALREADY been sent to MW so it doesn't get sent again? (I use batch files and flag fields)

           

          3] What is the plan if the data transfer fails or only partially completes?

           

          Just wanted to add my two cents worth again on this. Great that MW has such great features but you still are going to have to make the FileMaker side of it robust as well so that when someone is looking at the numbers in MW and they ask "Well John how did these numbers get here and where did they come from." you have a good answer and can explain the process to that person that has been setup in FM to insure the accuracy and vaidity of the data coming out of FM into MW.

           

          Good luck!

          1 of 1 people found this helpful
          • 2. Re: MoneyWorks and FileMaker Integration
            wrangler

            Hi Lemmtech

             

            Thank you for diving in first. Consideration number 1 that you listed is exactly what's been troubling me, but I didn't know enough even to be able to phrase a question about it, let alone do so without sounding utterly ignorant (which of course I am when it comes to accounting).

             

            With some exceptions I'm not even sure which data should originate in FM and which should originate in MoneyWorks. I can create the invoices easily in FileMaker and, I think, would need to pass these on to MoneyWorks. I can certainly set up a flag field to say an invoice has already been sent to MoneyWorks, and I would like to link the two records by some sort of key if that's possible so that we always know, from either end, where the data was sent to, and where it came from.

             

            I haven't set up anything in the solution yet for paying bills and I'm even less familiar (if that's possible) with that side of the business. But that's a whole area of inbound invoices and outbound payments that I'll need to deal with and again, I don't know what part of it should originate in FileMaker, and which in MoneyWorks.

             

            If an accountant is going to make sense easily of the business accounts each quarter and at the end of the financial year, everything has to end up in MoneyWorks even if it starts in FileMaker (?) 

             

            What sort of things need to be done by MoneyWorks( in addition to storing and calculating data that the accountant will need)? One huge area I'm not understanding is the relationship between the two applications - what data should be processed in FileMaker, what data should be pulled out of MoneyWorks and why, and when, and what data can or should only be processed by MoneyWorks?

             

            As I write this I'm hearing you say that I have to work with the accountant, with MoneyWorks, and with someone who's familiar with integrations like this

             

            As for "3] What is the plan if the data transfer fails or only partially completes?" - that's a nightmare I hadn't considered till you asked. Any chance you could tell me more about the batch files you create to keep track of all the accounting data you process in FileMaker?

             

            Regards,

             

            John

            • 3. Re: MoneyWorks and FileMaker Integration
              Lemmtech

              please post your email and I will answer your questions directly. thanks.

              • 4. Re: MoneyWorks and FileMaker Integration
                wrangler

                Thank you

                 

                john.nichol@me.com

                 

                Regards,

                 

                John

                • 5. Re: MoneyWorks and FileMaker Integration

                  Hi John & LemmTech,

                   

                  The main way that we keep track of the transfers is to always work in batches of invoices.

                   

                  For example, if we are invoicing a Block of the Month project with say 30 subscribers, then those 30 invoices are initially created by a script, with the standard postage or courier charge varied subsequently where the Customer has requested something different. We then check the MW balances using the MW-FM Plugin to pull that data from their individual records in MW for those 30 Customers and, if necessary, can modify the amount charged to a credit card should the Customer has missed paying the previous month due to her receiving a new Credit Card. We have a holding bin for the slow payers.

                   

                  The next stage is to charge the credit cards for those Customers who've told us they want to pay that way. We do this from FM's Web Viewer generating a URL for our secure Payment Gateway and meeting PCI Compliance. All the 30 invoices are then imported into MoneyWorks using the MW-FM Plugin. For the credit card charges that have been successful, we import those received payments into MoneyWorks. MoneyWorks matches the received money against the appropriate invoice and marks it as fully paid.

                   

                  At the end of each day we print a report from FileMaker of the credit card transactions and then make sure that the total matches the sum of credit card payments for each of the batches for that day as shown in MoneyWorks.

                   

                  There's a lot more detail that I can share with John Nichol but I hope the concept of working in batches can help him to see how to maintain a semblance of integrity between the two programs.

                   

                  Cheers,

                   

                  John

                  1 of 1 people found this helpful
                  • 6. Re: MoneyWorks and FileMaker Integration
                    Lemmtech

                    Yes John I do the same thing with my clients process invoices batches record the batch total in a log table stamp the batch number in each invoice record and record the same number in the accounting program to tie it all together. Thank you for your insights.

                     

                    -Mark

                    • 7. Re: MoneyWorks and FileMaker Integration
                      wrangler

                      Hi John,

                       

                      thank you for providing such a detailed outline of your invoicing process, and how FM and MoneyWorks handle it together. My head is spinning . I can feel myself defaulting to one little step at a time, just to understand all this.

                       

                      I get the invoice creation, and passing the invoices to MoneyWorks, even pulling out customer balances. I'm totally lost in the web viewer and processing CC transactions - the principle is fine, I just have no idea about the mechanics - but maybe I can ask you more about that and I'll understand it better when I reach that point. I certainly want to include CC transactions in the solution.

                       

                      But why of necessity handle invoices in batches? Mark does the same and I'm puzzled that the reason is so obvious to both of you and not at all clear to me. Could you not process single invoices the same way? (I'm sure there's something I'm missing here and I apologise for not being able to spot it. Maybe it's something that only becomes apparent through doing it?)

                       

                      I think my brain is full.

                       

                      Cheers,

                       

                      John

                      • 8. Re: MoneyWorks and FileMaker Integration
                        wrangler

                        Hi Mark,

                         

                        I understand all of that except (as in John's post) the necessity to process invoices in batches. What am I missing?

                         

                        Cheers, and thanks always,

                         

                        John

                        • 9. Re: MoneyWorks and FileMaker Integration
                          Lemmtech

                          There is no "need" to do it in batches but for example your process coudl transfer at the end of the day all the invoices that were created that day to MW or any other accounting program. Your batch record could say tsomething like this

                           

                          Batch #   Date   # of Invoices  Batch Total

                          1000       5-29-12           25              $9,650

                           

                          That way if you do a find in MW for batch # 1000 you should find 25 invoices that add up to that total if you don't they there is a problem. From a work flow perspective best to transfer all the invoices at one time at the end of the day and not each time it's unlikely you need them to be sent to MW in "real time".

                           

                          -Mark

                          • 10. Re: MoneyWorks and FileMaker Integration
                            wrangler

                            Hi Mark,

                             

                            I understand if it's a workflow thing. It does make more sense to do a single operation at the end of the day, rather than lots of single operations throughout the day. And the batch total as a double check makes sense.

                             

                            Thank you for your patience,

                             

                            John

                            • 11. Re: MoneyWorks and FileMaker Integration

                              Hi John,

                               

                              Using batches is nothing more than a way to keep a strict sequence of operations occuring in the correct order.

                               

                              Screen shot 2012-05-30 at 5.39.04 PM.png

                               

                              The Attached Screen shot is from our Invoicing layout. The blue buttons drive a sequence of scripted processes that we have to follow in order for the invoicing process to behave correctly. When we start a batch the StepDone containers are coloured a pale blue. They each turn to red when each step is completed. If, for example, you send the CC payments before posting the previously imported set of invoices (a manual step), then the payments cannot get allocated against the invoices. Instead they become OverPayments in each Customer's a/c and will need to be allocated manually to get those invoices "paid" and no longer showing in the MoneyWorks ACCREC listing.

                               

                              We can create a single invoice, and often do, but need to give it a new Batch No and follow the same sequence of steps before starting another batch.

                               

                              We have two staff members invoicing most days. The system allows them to work independently of each other without getting in each other's way. The System could easily handle a third or fourth person invoicing but we only have three MW licenses and insufficient transactions at present to justify an extra work station.

                               

                              I hope this brief description lets you to see the value of batch processing by enforcing the orderly sequence.

                               

                              Cheers,

                               

                              John

                              • 12. Re: MoneyWorks and FileMaker Integration
                                grantcowie

                                Hi John

                                 

                                You (and others) have raised a number of excellent issues with this.

                                 

                                First and most fundamental is the question of what should the cut-off point be FileMaker and MoneyWorks (i.e. which tasks are done in which package). This is going to be situation depend, but the general rule I think is, if it is a function that can be done in MoneyWorks let MoneyWorks do it unless there is a compelling reason to do it in FileMaker.

                                 

                                So for the example of generating invoices. The first question to answer is what is it about the invoice (or the generation of the invoice) that MoneyWorks can't handle? Having decided that there is some requirement to do it in FileMaker, then that becomes the entry point for invoices.

                                 

                                Now consider payments against invoices. Why would you want to record payments against invoices outside of MoneyWorks? Remember there are (possibly) the logistics of handling the physical money(cash, cheque, credit card whatever). If all the payments are funnelled through the "accounts department" and processed in a single manner, then you get better control over what is happening. So there has to be some really good reason (as there is in John Wolff's scenario) to take the processing outside of the accounting system.

                                 

                                So step one: decide what you need to use FileMaker for that MoneyWorks won't do, and have define clear cut-off boundaries (e.g. we have really complex invoicing requirements so will generate the invoices in FMP, but pass them over to MoneyWorks to do the receivables, tax, general ledger and (possibly) inventory management).

                                 

                                The second issue is the implementation of controls to ensure that things aren't transferred incorrectly (missed out, transferred twice, altered after transfer). The simplest way to do this is to have a batch number that is assigned to the transaction(s) when they are transferred -- and this is true of whether the batch is a single invoice or hundred. The steps are something like:

                                 

                                1. Send the invoices to MoneyWorks;

                                2. If successful, tag the FMP invoices with the latest batch number (increment each time);

                                 

                                Once an invoice has been allocated in batch number (i.e. has been transferred correctly), it should not be able to be modified in FMP (because the master copy is now in MoneyWorks, and you don't want two different copies). So you need to code for that in the FMP solution.

                                 

                                How do you know if a batch is successful? After the import get FileMaker to ask MoneyWorks to give you a count of the invoices with that batch number, and probably a total of the invoices (the right request to the plug-in will do this), and compare these with the totals calculated in FileMaker. This can be totally scripted as part of the FileMaker routine.

                                 

                                The final issue is working with an accountant. This is not really necessary, but what is necessary is an understanding of the MoneyWorks schema, and the field definitions (just the same as if you were working with someone else's FM database). So there is a separate learning curve to ascend in finding this all out -- it is all documented however (possibly unlike "someone else's FM database"). The field definitions, MoneyWorks functions and import/export routines are in the manual, and a simplified schema is available at the end of the MoneyWorks Automation Guide (on the developers page, the same place the plug-in is downloaded from). The plug-in "tutorial" also has lots of examples in it, and works with the MoneyWorks demo file so you ca have a good play.

                                 

                                Hope that helps the head spin ;-)

                                 

                                Cheers

                                Grant

                                • 13. Re: MoneyWorks and FileMaker Integration
                                  wrangler

                                  Hello John,

                                   

                                  thank you again for being so ready to share your knowledge and experience. The screen shot itself gives me some great insights into the process you've set up and the sequence you follow. I think I'm finally getting my head around the batch numbering issue (thank you John, Mark and Grant). It's the key that lets you confirm whether the invoices reached their destination in MoneyWorks safely. If MoneyWorks has the batch number, the number of invoices in that batch and the total amount for that batch agreeing with what's in FileMaker, it went through okay.

                                   

                                  I know this has been spelled out for me a numberr of times now, but I had to get to a point where I understood why it was happening, and therefore its place in the whole scheme. I get it now . This is pretty big for me.

                                   

                                  Thank you, thank you, thank you,

                                   

                                  John

                                  • 14. Re: MoneyWorks and FileMaker Integration
                                    wrangler

                                    Hi Grant,

                                     

                                    the head spin is slowing .

                                     

                                    I've definitely got the batch processing issue sorted now. In the solution I'm developing there are distinct workflow advantages to creating the invoices in FileMaker, but it makes sense then, as you say, to "pass them over to MoneyWorks to do the receivables, tax, general ledger and (possibly) inventory management."

                                     

                                    So there has to be some really good reason (as there is in John Wolff's scenario) to take the processing outside of the accounting system.

                                     

                                    Are you talking about the way John processes credit card payments in FileMaker? Otherwise I'm not understanding the whole "really good reason" thing. Simple is good for me, and if I can leave that whole receivables side to MoneyWorks it's a good thing. I really need to start playing with the MW Demo, don't I?

                                     

                                    The final issue is working with an accountant. This is not really necessary, but what is necessary is an understanding of the MoneyWorks schema, and the field definitions ... So there is a separate learning curve to ascend in finding this all out

                                     

                                    I've (very briefly) checked out the MoneyWorks schema at the end of the automation guide, and that head spin is picking up speed again. But I do see where I have to go now. Lots of learning ahead. I have to talk to the boss, and I have to get stuck into the demo and the plugin.

                                     

                                    Thank you for laying out such a detailed answer to my initial question, Grant, and the ones I posed later. You've been incredibly helpful - and you have great patience.

                                     

                                    Regards,

                                     

                                    John

                                    1 2 3 Previous Next