12 Replies Latest reply on May 28, 2014 11:48 AM by Stephen Huston

    wishful thinking, but is there a way?


      I have a sales database and a service database. I have many reasons why they can not be merged. So that aside, is there a way to have a user that is in the sales database have his 'new record' cross referenced with the service database to avoid duplications? I know the basics, search the other database, etc. I am just wondering if there is a creative way that allows a script to jump between open databases.

        • 1. Re: wishful thinking, but is there a way?

          As with anything FM always several ways to do things.  A couple of quick thoughts is that you could:


          1) Put a table occurrence of the service data in the sales data where it would be avaialble for use in the sales file.  Be aware of course you will need to check privillege sets to maintain your security.


          2) With proper file references set up, secutiry etc. you could call a script in another file and pass script parameters.



          It sounds like you want the sales to have access to the service but do not want the service to access the sales.  Very much doable. Many different approachs.  Times like this I step away from the computer head to the white board and map thinngs out before builtindg a thing.  Many times the first approach that I come up with comes up just a bit short for future growth.  Many times the solution to a problem merely changes the problem.

          • 2. Re: wishful thinking, but is there a way?
            Stephen Huston

            You can call a script in one file from a script in another file via the Perform Script step, as longs as you have an External Data Source in the original file which points to the target file — no need for a table occurance even.


            Use script parameters to pass the info you want the new script to use, usually setting these values via a $variable and then passing $variable as the script parameter. In the called script, redeclare the $variable as the first script step using

            • Set Variable [$variable ; Get (ScriptParameter)]

            You will need an External Data Reference from each file back to the other is you want to move both directions between files during scripts.


            You can also name the windows via script steps and then use the Select Window script step to land where you want to be at any point in the process, but especially at the end of the script, someplace that makes sense to the user rather than just wherever the processing finishes.

            • 3. Re: wishful thinking, but is there a way?

              I presume from what you say that the sales and services are separate files, so you are talking external data. Given that an option I think worth considering would be to create a third file to hold and manage contact data only, then modify your sales and services files to hold/manage only sales and services data and have both of them reference the contact file for its data. This would both overcome the duplication problem altogether and at the same time reduce the overhead in the other two files because neither would then have to manage contact data.

              • 4. Re: wishful thinking, but is there a way?
                Stephen Huston

                The same could be done with a third table added inside one of the existing two files — no need for another separate file. All of the existing tables in either file can reference the new table, in whichever file it gets placed, as long as all files have External Data Source references to the other file(s).

                • 5. Re: wishful thinking, but is there a way?

                  I agree. Equally, all three table could be within a single file. The reason I suggested a third file is it leaves aside a decision about which of the other two files to manage contacts within. There may be some logic to placing it in one file or the other, but if not, then as long as the requirement is that sales and services be separate files and not just separate tables, then to my mind it would be more logical to also manage contacts as a separate file.

                  • 6. Re: wishful thinking, but is there a way?
                    Stephen Huston

                    Ah, that makes perfect sense, depending on  why our writer says, "I have many reasons why they can not be merged."


                    When I read things like that without explanation or detail, I tend to wonder if it's a good reason, a bad reason, or just seems like to too much work.

                    • 7. Re: wishful thinking, but is there a way?

                      it may be my misunderstanding of what is possible. i figured they needed to be separate for one main reason; my service database has several related tables and relationships. i have around 2000 clients with nearly 17000 service id's. i also use this database for invoicing (same table, different layout) and inventory, etc. my sales databse is a clone with certain things removed, only as a different file (makes exporting a Win client wasy). it has maybe 4000 potential clients and around 800 quotes, We add several hundred companies through an excel import and then my sales people work from it and add there own as well. Although i could add these onto the main service database and custom find searches, etc. it still over populates my main database with too much waste and makes sceduling harder. in addition, my service database has each service order put into a status of 6 choices which are displayed on an opening dashboard.


                      can i use your suggestion without the service file showing these records?

                      • 8. Re: wishful thinking, but is there a way?

                        You could certainly contain all three main elements—contacts, services, sales—together with all their related tables within a single file. You would need to think through the process of doing so to make sure nothing got mucked up along the way, but it can certainly be done. There are many advantages to managing the whole box and dice within a single file, so it's worth considering.

                        • 9. Re: wishful thinking, but is there a way?
                          Stephen Huston

                          It will take some work initially, and some planning regarding permissions so that your data and your processes are protected both for and from the users who should or shouldn't see or use them.


                          The biggest benefit is that your total system overhead will actually be smaller, and any problems of records getting out of sync between the files will end.


                          Plot out the reasons for the current separation carefully, and then write your permissions and scripts with those restrictions in mind. The final result should be much cleaner and the final unified solution even smaller than the multi-file system.

                          • 10. Re: wishful thinking, but is there a way?

                            Thank you for the tips. It sounds like once I can free up time this is the right method. Just a simple and final question so I can better get my head around this.

                            As 'keywords' wrote, three main elements - contacts, services, sales together working in a single file. I currently have company, contacts, service with the second database being company, contacts, quotes. [ Company is the part that has thrown me off. I am still unclear on how to manage this. I really do not want my dispatch team to have to view or sift through company records that are not in the 'won' state.

                            As a clarification, my dispatch team uses the company table to call, plan and create routes. They add a service order through the company table. On a relationship table, the company is the hub that everything is joined to in one way or another.

                            • 11. Re: wishful thinking, but is there a way?

                              Merging them does not neccessarily connotate merging the actual data in the tables. Take the Sales file and make those tables within your service file, migrate the sales data to those new tables and create your layouts. Import your scripts and edit them so that they point at these new tables you created not the old ones.

                              • 12. Re: wishful thinking, but is there a way?
                                Stephen Huston

                                Even if you merge the data completely, you can restrict specific permission groups of users to only be able to read records with a specific status. Look into RLA (record level access) controls where you can use calculations to determine what is readable or editable for specific user groups. I still think merging tables which contain the same types of records will be your best bet in the long run.