I have made a little step forward, I now am able to script search recs with "searchpattern" and output to field in same table
set field [maindb::main_count_of_line1: Get ( FoundCount )
I then tried to duplicate this for the second search but it is putting text into the second field so I assume the first search is not being flushed. Am I getting warmer? :(
Thank you for your post.
Make sure you are performing the finds in the main table. You can then store that information to variables and update the information in the second table. For example:
Go to Layout [ <layout with first table fields> ]
Perform Find [ Restore ]
Set Variable [ $value1 ; Get ( FoundCount ) ]
Perform Find [ Restore ]
Set Variable [ $value2 ; Get ( FoundCount) ]
Go to Layout [ <layout with second table fields> ]
Set Field [ <second table>::field1 ; $value1 ]
Set Field [ <second table>::field2 ; $value2 ]
This should get you pointed in the right direction.
If you need clarification for any of the above steps, please let me know.
Thank you for your reply, I was starting to tear my hair out..
I have got as far as Set Field [ mytable::secondtabfield ; $value1 ] but I'm not sure if these are right? I get
Set Variable [$value1; Value:Get ( FoundCount )] and Set Field [statsdb::stats_line1_count = $value1]
The script runs to the end but doesn't put any values in and comes up with a 102 error in the debugging window.
This all makes sense now you have set it out for me.
The first thing I see is that you are using "Set Field" on two different tables. My script was not set up for that, so make sure you switch to the appropriate layout when you use Set Field.
Let's see if you can do this manually using the script as the model.
Go to the first table layout (mytable) and find for that subset of records. You should get a number of records found. If none are found, then that may cause a problem. Write down that number.
Perform a second find for the second group of records, and again, you should get a number of records found. Write down that number, too.
Now, switch to the layout that has your fields from the second table (statsdb) and enter the numbers into the appropriate fields. If one of those numbers go into one table, and the other into another table, make sure you switch to the appropriate layouts before entering the information.
Also, if you want to add a record before entering these values, then be sure to include a "New Record/Request" into the script before the Set Field step(s).
I hope this gets you closer to what you want.
I now have the script working and have 2 numbers entered into to fields on the same table, but :) it seems to be blanking the second Perform Find. I have set up the 2 specify find requests with different parameters i.e. "live" and "closed" and it finds the first set but then reverts back to the full found set when it comes to carry out the second Perform find. Apart from that it works a treat.
Is it best to keep the found totals in the same field or split them off into a separate one? My thought was that the stats would only be carried out once a month so to store the data on the same table would be wasteful as you would have blank fields. Is it a lot harder to script saving data to external table?
Thanks for your help, much appreciated.
Edited for typos
It sounds like your second Perform Find is restoring incorrect criteria. In your script, put a "Halt Script" command after your second "Perform Find". This halts the script, and you can view the results of the Find. Then, pull down the Records menu and select "Modify Last Find". This will show you the criteria that is being used to find the data. If this is not what you expected, then go back into the script and reset the criteria for the second Perform Find.
It is better to put the two values into separate fields. Otherwise, you may overwrite one value with the other. If the stats are carried out once a month, you may want to create a table with three fields:
That way, you can enter a new record for each month and present the totals.