AnsweredAssumed Answered

Can this script be made to run any faster?

Question asked by 5eppa on Nov 8, 2017
Latest reply on Nov 8, 2017 by psc

So we are setting up a database that among other things will take in a record for each call made on our VoIP network. This is ultimately used to calculate how much these calls are costing us each month and how much are we charging the clients to determine if we are making money off of the clients or not. The problem is we have a lot of clients so on average we get about 30000 calls in a month that go through this network. We get the information originally such that the company or client making the call comes through as just a number and it is up to us to actually associate that number to a company and service plan. We need these records to be summed each month and due to possible changes in Service Plans with clients it was decided that each month we would create a new service plan associated to the companies and that all the calls made by that company each month would then be associated to the Service Plans. I am pretty new to Filemaker and so I am unfamiliar with the scripting language and how to efficiently do things. I have also found that Filemaker seems to be somewhat limited in terms of things like arrays and as far as I can tell the ability to compare a record to another record in a separate table from what I can tell. Therefore the script I have written appears to work but is rather slow. I am wondering if someone could help me come up with a way to do this quicker as I am sure there is a way.

 

Go to Layout ["CDRs" (CDRs)]

Go to Record/Request/Page

     [First]

Loop

     Go to Layout ["ServicePlans"(ServicePlans)]

     Go to Record/Request/Page

     [First]

Loop

     Set Variable [$BID; Value:Companies::BID number}

     Set Variable[$Month; Value:ServicePlans::Month]

     Set Variable [$pk;Value:ServicePlans::_pkBID_CDRs]

     Go to Layout ["CDRs" (CDRs)]

     If [$BID =CDRs::CDRsBID and $Month = CDRs::Month]

          Set Field [CDRS:: fk;$pk]

     End If

Go to Layout ["ServicePlans" (ServicePlans)]

Go to Record/Request/Page

     [Next; Exit after last]

End Loop

Go to Layout ["CDRs" (CDRs)]

Go to Record/Request/Page

     [Next; Exit after last]

End Loop

 

So for some clarification the number to is matching up to each company based on what we initially get is called a BID. I have assigned these to the companies and set up each Service Plan so that they are associated to the companies and the BID is on that Layout. Now this script works at least when I have tested it in small doses such as say 10-15 records. The problem is that this will need to run each month and will take by the looks of things several hours to run over the course of 30000 records. That would annoy my boss greatly. So I am wondering if there is some way that I am not familiar with to make this process any faster. For example if an array existed I could just pass through all the service plans and store there information the first time so that I do not continually need to jump to that layout and table. Or is there a way to compare these records without making that jump? Or is there something else that would decrease the shear number of calculations and jumps by any amount. Anything to save one or two calculations here would save a lot of time when multiplied over so many records. Hopefully that all makes sense and I thank you in advance for any advice you can give.

Outcomes