First of all we dont know what criteria you are restoring in your Perform Find, or if its even needed.
Second, something doesnt quite add up. A report is based on the found set. Therefore, if you findfor certain clients, the report will be based on those two client records. If you need to go to their records in another table lets say for activities, then you would need a Go to Related Records step. Please clarify your structure.
Hi, thanks for the response. I'll try to clarify the problem, if I can...
The owner of my company works with many clients, all of which have multiple years of information on the nutrient content of their livestocks' manure. The owner takes the nutrient information and writes recommendations for the application of said manure based on rolling averages for each nutrient. So, if a client has data from 2001-2009, but the owner only wants to include information from 2005-2009 to determine an average, then what he would like to do is find all the info on the client of interest, then be able to select (Yes/No) which years of information to include in the determination of the average. I know this could be accomplished by performing a find for a client and particular years, but I would like to make this as seemless as possible for the owner. The solution I want to arrive at is: the owner can perform a find for a client. Once he has the client information, he then needs to be able to select which years of information to include. At this point, I would like him to be able to run a script that will then take the selected years (for only the client he originally selected) and generate an average for the nutrients associated with each year.
Can you follow all that? Let me know if you need any additional clarification and get back to me as soon as you can!
I think that it would make more sense to have the user select the years that he wants to run the report for when he is on the client record. This could be captured using a global field and a checkbox on either the same layout, a new layout, or a popup window, etc. The UI is your choice.
Then when they hit the button, if would run your script.
Your script would loop through the selected choices for the year and do the find for him. Then the report is done.
Or another option is to use a new relationship and the global field to base your average off of. IOW, create relationship keyed from the global text field to a Year field in your historical records as well as ClientID to ClientID. Create a summary field in the historical table that averages the cost.
Now put that related summary field on the parent Client layout. When your user selects multiple years or changes the years it is should change.
I agree, and just wanted to add this:
You can define the value list to show only related year values, starting from Clients - so that the checbox set always shows only relevant choices (if using a global field, it would make sense to clear it upon loading a new client record).
Hi, thanks again for the response. I'm sorry I didn't get back to you sooner; creating the database for my employer is my second job with this employer. Anyway...
Could you give me some direction on how to use the global field as you suggest in your first solution. I have never used global fields in my databases and after reading about them, I don't understand how they would fit into my current problem. Your suggestion makes me think of making a portal for all the records for the individual, then somehow selecting the years within the portal that the report would be generated on, but I know that's not correct.
Sorry to be so naive...