Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Bimsickle
 Share

Recommended Posts

I have a script that builds a HTML List and saves it as a variable used to display a cross tab report.

 

If I run a full script it can be quite slow.

 

The process is

 

Go to Sales_Lines table

find customer type, Date range

loop

Selected Rep/reps

loop

selected products

end loop

end loop

 

then on each line I grab the customer ID, and check the date range, if it falls into previous year, check product ID against first in list, then loop through following products.

Next line, check again.

 

For speed purposes, would it be quicker to perform multiple finds & sorts rather than one find, with multiple checks?

Each line currently get checked

If the Customer ID is the same as the last

If the date is in the range of last year or this year

If product ID = group of ID's

if product ID = x on list (looping through the list).

 

Next line

 

As a change, perform a find for set things

 

Initial find sales within range for selected products/ reps

Build a list of all valid customer ID's

 

then a find for product 1 within date range x

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

then find for product 2 with date range x

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

then a find for product 1 within date range xx

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

And a find for product 2 within date range xx

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

 

I just don't want to write the monster script again if it won't be any faster.

 

There currently is

5,500 Expected customer ID's

300,500 matched items lines out of 590,650 possible

21 reps

23 products

2 date ranges

Link to comment
Share on other sites

I made a sample to address another question, so I will attached the same file.

 

Although this one is a little buggy from memory.

 

I have made a few additions like adding a growth calculation before adding the record to the final line.

 

It's just doing so many checks on each line, I was thinking it may be quicker to do a search for the list of ID's, then multiple searches for each "check"...

 

It's pretty quick(ish) for one rep, about a minute or two, but takes almost an hour for 20.

TableSorter_CrossTab.fmp12.zip

Link to comment
Share on other sites

You're doing a multiple-request Find in which each Find request includes a value in Customers::Sales Rep, which is a table two relationships away from the Lines table where you're doing the find.

 

Yeah, that's going to be dog-slow all right.

 

Create a field in Lines, Sales Rep, and set it up to be a lookup field that looks up values from Customers.

 

Then in your script, do your find on Lines::Sales Rep not on the distant related field Customers::Sales Rep.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use