Summarize Counts in a Separate Layout

[CatLady03]

I have several reports where I count the number of records found based on specific search criteria. I can't figure out how to get the counts from each report as hard data that I can display on a separate, single layout.

If there is anyone out there who can help, I would sure appreciate it.

[Ender]

Can you give an example of the different ways you need to count the data? Do you have a separate file for holding these counts?

[CatLady03]

I use the summary field called Count and I count one field that appears in every record. The problem is that all reports are in the same file. Is there a way around making separate files to hold the counts or is that actually the answer?

Thanks for your help...I really appreciate it.

[Ender]

Sorry, that's still not enough info for me to make a recommendation about the best approach.

[CatLady03]

I'm sorry, but I guess I don't know what you need to know. Can we maybe chat about this a bit and I can answer your questions directly? That or can you be more specific in what you need to know? I can provide screen shots if you need them...whatever I can do to get you what you need to help me.

Edited March 19, 2006 by Guest

[Ender]

To start with, what are you counting (it's helpful to know what we're talking about)?

What is the current structure of the file(s) holding this data?

What's significant about each count you want to save? Are they just groups of records that a user may happen to find and think, "Hey, I should save this count!" Or is this part of some process where you need a count for each possible value of a field?

Is it important that these counts are available in Browse Mode, or can they simply be viewed/printed once assembed?

[CatLady03]

Thank you for providing direct questions. I will answer each one as best I can:

1. I am counting a text field to get the number of records that the "find" produces. The application is stock screening. The field counted is the stock symbol for each record.

2. I have a total of 4 files, 3 of which feed into the "master," so to speak. I don't use portals...I only use relationships between the 4 files. I have numerous layouts for menus, data display, reporting, stock screening, etc. The only way data is entered into the database is through imports from different Excel source files. I hope this is all you need here.

3. My boss wants to know how many stocks each of his searches produces. He wants a single summary page that contains the name of the query and the number of stocks the query picked, i.e., a summary of the results.

4. The counts can be viewed/printed and are not necessary in browse mode. I have a Leading Grand Summary part containing the count field, so each count is displayed on the first page of it's associated report.

I hope this is sufficient information...thanks!!

[Ender]

There's a couple different approaches, but the easiest is probably to simply find all the "stock screening(?)" records by allowing multiple Find requests, then use a columnar report with sub-summary parts to show the counts for each stock symbol. The part setup would look like:

Header

---------------

Sub-Summary by Stock Symbol

---------------

Footer

Put the Stock Symbol field and the Count summary field on the Sub-Summary by Stock Symbol part.

When sorted by Stock Symbol, and Previewed or printed, the sub-summary parts show up.

[CatLady03]

I think I understand what you're saying and it works for the first count. All subsequent counts end up being duplicates of the last count instead of each individual count.

I think I need to further explain what I'm doing. I have 8 separate queries (find requests) that produce a certain number of stocks, each of which has its own symbol. I want to count how many stocks are produced by each query. This count is displayed on the related report. There are 8 separate report layouts. My goal is to display each of the 8 counts in another separate layout (Report Summary) that has the name of the query and its associated count.

Your solution, if I understand things correctly, would still only produce one count, because you can only have one sub-summary in a layout. Repeating the count field in the sub-summary would only produce the same count 8 times, which is what I'm experiencing now.

I have tried creating 8 separate count fields and naming them as they relate to the specific query, but they all come up with the same count duplicated in each field. In the Report Summary layout(screen shot attached), my data is in the body of the layout. I do realize that you can only count in a summary part, but I was only trying to display the count that had already been achieved by running the query. I just can't make the count stay...it's always replaced by the next count.

If I can provide any other screen shots that would be helpful, please let me know.

Report_Summary_Screen.pdf

[Ender]

The screenshot helped. This is what my previous questions were getting at.

For this, I'd recommend a second file to hold each saved count. Call the file "Stock_Summary", with fields Date, Description, and Count.

You'll need to have scripts populate each new record in this file with the Date, Description, and Count. Everytime a Find is made that the user wants to Save, they hit a button that sets a global with the value of the current Count, switches to the Stock_Summary file, creates a record, populates the Date and Count, and asks them to fill in the description (or have each "report" of yours set the description.)

You'd then need a process to show and print the Stock_Summary records. You could find them all (making sure they get cleared out each time,) or keep the old Stock_Summary records there, and have your report script allow finding for a specific date.

[CatLady03]

OK, you're way over my head now...sorry about that. I follow you for a while, but I get lost at the end.

When you say the there should be a button that "sets a global with the value of the current count," I don't know how to do that. I know how to create a button...I just don't know what process to go through to set a global field with the resultant count from a given query. I've also never used Global fields, although I think I understand how they work.

What I'm getting from this is that I need a single separate file that holds the 8 different counts, each being a global field that is set using a process that I don't yet understand. I would actually create the Report Summary layout that I showed you in this separate file. Each of the global fields would display next to the related query name.

I hope I'm getting close to understanding this. I really do appreciate your time and assistance.

Edited March 19, 2006 by Guest
Further understanding achieved...

[Ender]

"Global" is a field type in the Define Fields dialog. You would only need one global (number) field for passing the Count to the other file. If your reports are also passing the Description, then make a global (text) field for that too. Here's an example script:

Stock System Rating Report

//In Stock Screening file

Perform Find [ Restore ]

//Find records that match Stock System Rating criteria

Set Field [ gTempCount ; Status(FoundCount) ]

Set Field [ gTempDescription ; "Stock System Rating" ]

Perform Script [ external (Stock_Summary): Add Summary ]

Refresh Window [ bring to front ]

Add Summary

//In Stock_Summary file

Enter Browse Mode

New Record/Request

Set Field [ Date ; status(currentDate) ]

Set Field [ Description ; Stock Screening::gTempDescription ]

Set Field [ Count ; Stock_Screening::gTempCount ]

Exit Record

This assumes a relationship in the Stock_Summary file to the Stock Screening file. The match fields for this relationship doesn't, since it's only pulling a global. The "Add Summary" script in the Stock_Summary file could be used by each report in the Stock Screeing file.

There are a number of other options and details that might be included in such a process, but that should give you the idea.

Once the records are added to the Stock Summary file, use a standard List View to show/print the Counts for each Description.

BTW: It might be helpful if you set your skill level to "Beginner" or "Novice", so we don't start off too technical in the future.

[CatLady03]

I guess I didn't realize that with all the work I've done with this software that I was only a beginner, but I can see that now. The problem is I don't know how to reset my level within my profile. I've looked, but I don't see where it can be changed.

I will try to do what you've described. It's all new to me, so wish me luck.

Thank you for your time. I hope that I'm free to get back to you if I still have questions.

[CatLady03]

I've been working with your solution and find that I'm still not understanding, I'm afraid. I need to relate the different file names that you're using to what I actually have.

Can I assume that the Stock Screening File you refer to would be the file where my screening (the "finds", if you will) takes place? If so, that would be what I consider my master file, which I have named "IIVA Database."

The "Stock Summary File" that you refer to would be the new file I created to contain the counts and the Report Summary screen itself, which I call the "Stock Screening Summary" file.

If my understanding is clear, I'm going to:

• Create the first script (I'm calling it, "Set Count") in the IIVA Database file
  
• Create the Add Summary script in the Stock Screening Summary file
  

If this is correct, then at least I understand something. I have never used the Set Field command either, so I'm really getting an education here.

Also, if I understand correctly, you said that the Add Summary script should be added to each of the screening reports in the IIVA Database file, which would create the count that's used by the Stock Screening Summary file where my Report Summary layout exists, correct?

Now, I'm still not getting how to setup the Report Summary layout to reflect the 8 different counts that these other scripts produce. There's still a piece (or two) missing in my mind.

I'm sorry I'm not getting this. I realize it's probably tedious for you, but I know now that I can't figure this out on my own. This is way beyond my skill level. I had a feeling it would be when my boss first asked me to create this summary of counts.

Edited March 19, 2006 by Guest
I understand about the relationship between the files now...

[CatLady03]

OK, something just sunk in about how the scripts work, but it's not all there yet. I see how the script being added to the screening report passes the record to the Summary file, but I don't yet understand how to display Summary file record. I tried putting the count in a layout and it's blank in both Browse and Preview modes, yet there's a record in the file.

The more I understand, the more confused I get... :

[CatLady03]

Please...is there any way we can do a chat together on this??

[CatLady03]

I'm still getting the same record repeating on the Stock Screen Summary layout in Preview mode. I have attached a PDF file with some screen shots that I think are relevant so that maybe you can see where I'm going wrong.

Where I talk about running the next script for the next report, I simply created scripts like these for each record I wanted passed to the Summary file.

My problem is that I can't see the logic of only one iteration to get 8 separate records passed to the Summary file. It seems logical that I would need to create the scripts individually, thereby passing a record each time a report or query is run. Obviously, my logic is flawed because I'm not getting the results I expect.

Could you please ask some relevant questions that I might answer to further your insight into what I'm trying to accomplish here?

You made it sound so simple and yet I can't seem to make it work. Anything you can do would be greatly appreciated.

Thanks...

P.S. This is the script called, "Stock Screening Summary" so you know what it contains, too.

[color:red]Stock Screening Summary

Go to Layout [ Stock Screening Summary ]

Enter Preview Mode

[ Pause ]

Perform Script [ Filename: "IIVA Database.fp5", "Menu - Pre-Set Stock Screen Reports" ]

[ Sub-scripts ]

The last script just takes me back to a menu.

Screen_Shots_-_FM_Forum.pdf

Edited March 19, 2006 by Guest
Needed to add the P.S. because I forgot this information in the attached PDF file

[Ender]

The Count and Description fields in Stock Screening Summary should be regular number and text fields, respectively. The globals are only needed in the parent file for passing the values to the sub-script.

[CatLady03]

YOU ARE THE MAN!!!!!

I can't tell you how much I appreciate your help. I realize that I have inundated you a bit with all my posts, but I just didn't know any other way to do this.

Thank you so VERY much!! :thumbup:

[CatLady03]

I have one more little problem...the counts that I had displaying on the individual reports come up as a question mark now. Do you know what I might attribute that to? I have a Summary field called Symbol Count in the definitions, so I'm not trying to display the global field. I don't understand what would have changed my ability to display this field. It's in a Leading Grand Summary part and is not overlapping any other part.

Your help, as always, is greatly appreciated...thanks a bunch!

[CatLady03]

This one resolved itself. I'm not sure what I did, but it's OK now.