Jump to content

Perform Find/Filter "Parent" based on "Child" quantities


This topic is 3495 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

To begin, I have about 100,000 records (and growing) in a "Parent" table. Each Parent will have at least one "Child" (usually about 2 or 3, but can be 5 or 6). Each Child has a "Quantity" calculation field {sum( in - out)} which is related to the "Transactions" table.

 

I'm trying to filter a table (in list view) of the "Product" aka. parent to show only products that have any "Inventory" items aka. child that have quantity > 0.

 

I'm able to generate the results, my problem is the speed at which it calculates

 

For instance, "Prod A" might have three children:
A_1, with qty 1

A_2, with qty 0

A_3, with qty 0

 

"Prod B":

B_1, with qty 0

B_2, with qty 0

 

"Prod C":

C_1, with qty 0

C_2, with qty 0

C_3, with qty 5

C_4, with qty 7

 

Therefore, I created a calculation field called "invQty"in the Product table as such: sum (inv::quantity). I have a button on my product layout attached to a script that looks for a value > 0 in the invQty field.

 

The result of the above example will display "Prod A" and "Prod C" only, which is great but it takes about 45 seconds. (Script is performed on client, file is hosted on FMS)

 

Would this be a time to use executeSQL, if so, how would the calculation be written (I have yet to use this function) from what i've read it isn't much faster? Otherwise, is there a faster way to do this? You guys always come up with some clever stuff, hope you can help me.

 

Additional thoughts:

I've been working with PSoS lately, and it's been amazing, but not sure if this can work here to get a found set to display on client.

I've also been looking into the "MasterDetail" setup by Todd Geist. Pretty awesome stuff, just not sure if it's the right solution for me yet.

 

Thanks in advance to everyone.. I also attached an image of the relationship for this example. I put a lot of information for this simple task, just wanted to be clear as possible.

 

 

 

post-111830-0-89626400-1409255721_thumb.

Link to comment
Share on other sites

Searching on a related unstored calculation is predictably slow. ExecuteSQL is not inherently any faster. PSoS would almost certainly be faster. What you'd do after the Find is: gather the IDs of the found set, e.g. using the Summary field "List of" option; and exit the script with that as the Result. Back on the client, use Get(ScriptResult); set it into a global field; and go to related records -- you'll need a relationship from that global field to the Product ID.

 

Another approach could be to create a qty calc in the Transaction table (which could be a stored calc since it wouldn't reference related data); do your Find in that table; and go to related records from there.

 

You might also consider scripting the inventory in/out process so that it stores the current quantity at the Inventory or Product level. Finds are super fast on non-calculated number fields.

Link to comment
Share on other sites

  • Newbies

I'm using the PSoS recommendation. It gives me the result in about 11 seconds, but at least it's consistent and not reliant on the clients computer/internet speed. More importantly, your clear explanation helped me wrap my head around this function to use in other situations.

 

Thank you Tom!

Link to comment
Share on other sites

Keep in mind that your test results may not hold up in real-life depending on the # of users that will execute this at the same time and the specs of the server.

 

Using PSoS forces processing onto the server that used to be distributed on the clients.  While it may be faster in testing mode with a development server that is not busy and single instances of PSoS being triggered, things may look very different when multiple PSoS sessions are spawned on a server that is otherwise already busy (backups, schedules, regular client-server activity,...)

  • Like 1
Link to comment
Share on other sites

  • Newbies

Agreed. Thank you guys for the follow up input. The number of users and data should be increasing faster for sure. In this case, Tom's 3rd recommendation is probably best (scripting current quantity value and store on product level). I will make this script step a PSoS (uncheck, wait on server), so it doesn't slow down the user while the in/out process takes place.

Link to comment
Share on other sites

This topic is 3495 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.