Home

Macros and Programming

 

CardboxForumsMacros and Programming > "Counting the Number of Records"

Counting the Number of Records

I need Cardbox to count how many records match a search, and put this number in a field.

Current user: [none]
Register / Log In · Help

Posted By Post

Voices4Wellbeing

18-Dec-2008 13:39

Hi,

My database has an Equal Opportunities section. It uses either check boxes or radio buttons to record various information such as ethnicity, age bracket, gender etc.

I need to produce a report that tells me how many people there are in each 'section'. I.e The report would say: Male 36 Female 22 etc.

I have been told by Cardbox Support that I need to create a format for the report, and use a macro to use the 'count' command, but I don't know how to get it to count the number of records in a search and add this to a field. Nor do I know how to get it to perform multiple searches (for example I want to press a button and it would search for how many males and put the number in the 'male' field, then search for how many females and put the number in the 'female' field, and then search for all the different ethnicities etc (which are in different fields).

I apologise if I am not explaining it very well. If it does not make sense just ask I will try to clarify it for you.

A thank you in advance for your assistance.

Charles Welling

19-Dec-2008 07:43

Records in a selection are counted by the command RecordCount.
Let's suppose you have a field called "GENDER", as well as the fields "TOTAL_MALE" and "TOTAL_FEMALE".

I should warn you though that the use of fields for storing results of selections is asking for trouble if not properly used. E.g. the field "TOTAL_MALE" will be present in ALL records. The result of your first selection will be stored in the first or the last record of this selection, depending on what your macro does. Each and every time your macro runs, a result will be stored in one of your records, which may (and one day will) lead to improper reports. There are two ways to prevent this:

1. Print your reports by using a macro and have this macro clear all fields that contain results after the print job.
2. A better option is to create a separate "RESULTS" database, which contains just the few fields for storing the results and a format for printing. This database would never need to contain more than a single record for the latest results, but it could have a "DATE" field as well and store the results incrementally, including the date of the report. This would create a history of results and you could reproduce older reports and even produce monthly or yearly reports that show the results over a period of time.
This option is not more complicated than option 1.

If you would decide for option 2, please let this forum know so you can have an example of how to set up a macro that stores the results in the second database.

But to start with option 1, here's a macro that selects all males from the field "GENDER" and stores the result in the first record of that selection.

SelectionLevel=0
Cardbox.Select "GENDER","male"
FirstRecord
EditRecord
fields("TOTAL_MALE")=RecordCount
SaveRecord

Voices4Wellbeing

19-Dec-2008 08:45

Thank you for your reply Charles, it is very helpful.

Yes, having it add to a record would not be suitable, for reasons you stated.

I would prefer option 2, so I could set up a second datatbase that had ALL the different categories on, and cardbox then counted them all and put the number in each field.

Would it be possible for one macro to search for TOTAL_MALE and put this number into a field I designate, and then also run consecutive searches for TOTAL_FEMALE, and then also the ethnicity/sexuality/religion fields too?

Charles Welling

19-Dec-2008 11:39

Here's a basic macro that will get you started. It assumes that your main database is called "MyDatabase" (you'll have to change that of course into the actual name), and that the reports database is called Reports.
The Reports database contains the fields DATE, TOTAL_MALE and TOTAL_FEMALE. When you make that database you can make as many fields as you want and expand the macro.

I'll explain what the macro does. The first 7 lines make the Reports database ready for use, and if it hasn't already been opened, Cardbox opens it first. The first line tells Cardbox NOT to stop when an error occurs. The second line looks if there is a window called "Reports" (=the Reports database) and if so, it will refer to this window as "reports". If this is not possible because Reports has not yet been opened, an error occurs and Cardbox will execute the lines in the IF statement, i.e. it will open Reports.

Then Cardbox makes two selections after another and temporarily store the results in "total_male" and "total_female". If you need more selections, just copy such a three-line section, change the field name and the variable that stores the result. Selecting ethnicity could be like:

SelectionLevel=0
Cardbox.Select "ETHNICITY","caucasian"
total_ethnicity=RecordCount

etc.

After all the selections have been made, Cardbox switches to "Reports", adds a blank record and puts the results in the appropriate fields, including today's date. It then saves the record.
Your done!

Remember these are the basics. Start experimenting and you can always make improvements.

*************

on error resume next
set reports=Windows.item("Reports")
if err.number <> 0 then
OpenFile "cardbox://cardbox/Reports"
set reports=Windows.item("Reports")
Activate "MyDatabase"
end if

SelectionLevel=0
Cardbox.Select "GENDER","male"
total_male=RecordCount

SelectionLevel=0
Cardbox.Select "GENDER","female"
total_female=RecordCount

reports.AddRecord

reports.ActiveRecord.Fields("DATE")=DatetoCardbox(date, ".")
reports.ActiveRecord.Fields("TOTAL_MALE")=total_male
reports.ActiveRecord.Fields("TOTAL_FEMALE")=total_female

reports.SaveRecord

Voices4Wellbeing

19-Dec-2008 12:50

You are quick! Thank you so much. I'll try this and let you know how I get on.

Thank you again, you are a life saver!

Charles Welling

20-Dec-2008 06:59

PS. There's one small thing I forgot to point out to you. The line:

OpenFile "cardbox://cardbox/Reports"

will only work correctly if the Reports database is on a server (not on a local disk) and this server is called "cardbox". When your server would be called "MyServer", the correct line would read:

OpenFile "cardbox://MyServer/Reports"

When your not sure about the name of your server or when it resides somewhere on a local disk, start Cardbox, click Tools > Record, and open Reports. Copy the result of the newly recorded macro (2 lines) to my sample macro, delete the old ones and it will work.

Voices4Wellbeing

22-Dec-2008 12:27

Thanks for that. I'm having some teething problems with it though. I tried it out with your example, and it worked. I then extended it to include all the fields I needed it to. Again, it was successful, except I noticed one of the fields on the reports page was empty. I checked my macro and amended the spelling for that record. It no longer works. It runs (the screen flashes a bit as it runs the searches), but it does not add a new record to Reports or add any numbers to the fields. Here is my full macro:

on error resume next
set reports=Windows.item("Reports")
if err.number <> 0 then
OpenFile "C:\shared\CARDBOX\DATATBASE FILES - DO NOT TOUCH\Reports.fil"
Activate "LINk2"
end if

SelectionLevel=0
Cardbox.Select "GENDER","male"
total_male=RecordCount
SelectionLevel=0
Cardbox.Select "GENDER","female"
total_female=RecordCount
SelectionLevel=0
Cardbox.Select "GENDER","transgender"
total_transgender=RecordCount

SelectionLevel=0
Cardbox.Select "RELIGION","islam"
total_islam=RecordCount
SelectionLevel=0
Cardbox.Select "RELIGION","churchofengland"
total_churchofengland=RecordCount
SelectionLevel=0
Cardbox.Select "RELIGION","anglican"
total_anglican=RecordCount
SelectionLevel=0
Cardbox.Select "RELIGION","romancatholic"
total_romancatholic=RecordCount
SelectionLevel=0
Cardbox.Select "RELIGION","muslim"
total_muslim=RecordCount

SelectionLevel=0
Cardbox.Select "SEXUALITY","heterosexual"
total_heterosexual=RecordCount
SelectionLevel=0
Cardbox.Select "SEXUALITY","bisexual"
total_bisexual=RecordCount
SelectionLevel=0
Cardbox.Select "SEXUALITY","gay"
total_gay=RecordCount
SelectionLevel=0
Cardbox.Select "SEXUALITY","lesbian"
total_lesbian=RecordCount

SelectionLevel=0
Cardbox.Select "CARER","yes"
total_yes=RecordCount
SelectionLevel=0
Cardbox.Select "CARER","no"
total_no=RecordCount

SelectionLevel=0
Cardbox.Select "DISABLED","yes1"
total_yes1=RecordCount
SelectionLevel=0
Cardbox.Select "DISABLED","no1"
total_no1=RecordCount

SelectionLevel=0
Cardbox.Select "AGE","16to24"
total_16to24=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","25to34"
total_25to34=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","35to44"
total_35to44=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","45to54"
total_45to54=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","55to64"
total_55to64=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","65to74"
total_65to74=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","75to84"
total_75to84=RecordCount
SelectionLevel=0
Cardbox.Select "AGE","85up"
total_85up=RecordCount

SelectionLevel=0
Cardbox.Select "ETHNICITY","whitebritish"
total_whitebritish=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","whiteirish"
total_whiteirish=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","blackbritish"
total_blackbritish=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","blackcarribean"
total_blackcarribean=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","blackafrican"
total_blackafrican=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","asianindian"
total_asianindian=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","asianpakistani"
total_asianpakistani=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","asianbangladeshi"
total_asianbangladeshi=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","chinese"
total_chinese=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","whitecarribean"
total_whitecarribean=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","whiteafrican"
total_whiteafrican=RecordCount
SelectionLevel=0
Cardbox.Select "ETHNICITY","whiteasian"
total_whiteasian=RecordCount

reports.AddRecord
reports.ActiveRecord.Fields("DATE")=DatetoCardbox(date, ".")
reports.ActiveRecord.Fields("TOTAL_MALE")=total_male
reports.ActiveRecord.Fields("TOTAL_FEMALE")=total_female
reports.ActiveRecord.Fields("TOTAL_TRANSGENDER")=total_transgender

reports.ActiveRecord.Fields("TOTAL_ISLAM")=total_islam
reports.ActiveRecord.Fields("TOTAL_CHURCHOFENGLAND")=total_churchofengland
reports.ActiveRecord.Fields("TOTAL_ANGLICAN")=total_anglican
reports.ActiveRecord.Fields("TOTAL_ROMANCATHOLIC")=total_romancatholic
reports.ActiveRecord.Fields("TOTAL_MUSLIM")=total_muslim

reports.ActiveRecord.Fields("TOTAL_HETEROSEXUAL")=total_heterosexual
reports.ActiveRecord.Fields("TOTAL_BISEXUAL")=total_bisexual
reports.ActiveRecord.Fields("TOTAL_GAY")=total_gay
reports.ActiveRecord.Fields("TOTAL_LESBIAN")=total_lesbian

reports.ActiveRecord.Fields("TOTAL_YES")=total_yes
reports.ActiveRecord.Fields("TOTAL_NO")=total_no

reports.ActiveRecord.Fields("TOTAL_YES1")=total_yes1
reports.ActiveRecord.Fields("TOTAL_NO1")=total_no1

reports.ActiveRecord.Fields("TOTAL_16TO24")=total_16to24
reports.ActiveRecord.Fields("TOTAL_25TO34")=total_25to34
reports.ActiveRecord.Fields("TOTAL_35TO44")=total_35to44
reports.ActiveRecord.Fields("TOTAL_45TO54")=total_45to54
reports.ActiveRecord.Fields("TOTAL_55TO64")=total_55to64
reports.ActiveRecord.Fields("TOTAL_65TO74")=total_65to74
reports.ActiveRecord.Fields("TOTAL_75TO84")=total_75to84
reports.ActiveRecord.Fields("TOTAL_85UP")=total_85up

reports.ActiveRecord.Fields("TOTAL_WHITEBRITISH")=total_whitebritish
reports.ActiveRecord.Fields("TOTAL_WHITEIRISH")=total_whiteirish
reports.ActiveRecord.Fields("TOTAL_BLACKBRITISH")=total_blackbritish
reports.ActiveRecord.Fields("TOTAL_BLACKCARRIBEAN")=total_blackcarribean
reports.ActiveRecord.Fields("TOTAL_BLACKAFRICAN")=total_blackafrican
reports.ActiveRecord.Fields("TOTAL_ASIANINDIAN")=total_asianindian
reports.ActiveRecord.Fields("TOTAL_ASIANPAKISTANI")=total_asianpakistani
reports.ActiveRecord.Fields("TOTAL_ASIANBANGLADESHI")=total_asianbangladeshi
reports.ActiveRecord.Fields("TOTAL_CHINESE")=total_chinese
reports.ActiveRecord.Fields("TOTAL_WHITECARRIBEAN")=total_whitecarribean
reports.ActiveRecord.Fields("TOTAL_WHITEAFRICAN")=total_whiteafrican
reports.ActiveRecord.Fields("TOTAL_WHITEASIAN")=total_whiteasian
reports.SaveRecord

I cannot see where it has gone wrong, can you?

Voices4Wellbeing

22-Dec-2008 12:44

By the way, having seen what it can do, you have come up with the perfect solution to my problem. It's amazing how it is exactly how I wanted it to work.

(As long as I can get it working again, haha)

bert

22-Dec-2008 17:17

Hi,
If report was not opened already, the macro does open the db.
However, after opening the db, the object reports was not set.
So add after line 4: set reports=Windows.item("Reports")

Also I should add after "end if" a line with on error goto 0. This because you will not get proper error messages after on error resume next.

Then: I do not know a Cardbox command "select 16to24". I think you have to change this to "16:24" en "85up" has to be "85:". But perhaps are that used terms in you db?

With this I think you have found some errors. If it still not works, a line number will be reported by Cardbox.

Success
Bert

Mary Doyle

22-Dec-2008 21:07

Re:
Then: I do not know a Cardbox command "select 16to24". I think you have to change this to "16:24" en "85up" has to be "85:". But perhaps are that used terms in you db?

If you have an AGE field with the text "16to24" etc you will need to use macro equivalent of the Search Data command, i.e. SearchData, as the normal Search command won't select alpha-numeric text.

e.g.
SelectData "AGE","16to24"

Hopefully you will find success. I too find using separate databases for search results very useful.

Mary

Voices4Wellbeing

23-Dec-2008 09:24

That you bert, and mary.

I added that line and it works perfectly. I did not have any problems with my 16to24 selections either (I didn't change it, it works fine).

16to24 is an radio button option. It's just the name of the field. As is 25-34, and 35-24 etc.

I tested it and it works perfectly.

Again, a big thank you to Charles, and also to Bert and Mary. You've been great!

Charles Welling

23-Dec-2008 18:55

You see I'm not always that quick: I had a day off so I'm late in responding. But as Bert had already indicated: you accidentally deleted a line from the original macro.

You're absolutely right about the "16to24". If your database was created by Cardbox 3 and not by an earlier version, it will index "16to24" exactly as it is: as a word. For details look at the Help file under "controlling number recognition".

I'm glad you liked my suggestion.

By the way: you forgot to include agnostic blackscots.

Mary Doyle

23-Dec-2008 19:39

Charles,

You are quite right - I tried 16to24 in a database created pre-2003 and a later one. The Search command was fine for the later one. The earlier database would only find the record with the Data Search command.

May I wish all of you who are not agnostic blackscots, etc., a very Happy Christmas.

Mary

bert

23-Dec-2008 20:24

Hi,

16to24 was never a problem: you only had to read the manual about colleting sequences.

Add statement "checkwholeterm" and this *problem* was solved. Checkwholeterm is now default in CB3. It was left away for Cardbox let working on machines from the stones era.

Because I did not know that 16to24 a term was that was used in the db, I wondered if it was the right statement for selecting records containing numbers from 16 to 24.
But that is clear now.

regards
bert

jason

16-Aug-2010 03:52

can anyone help me with this?

args1(0).Value = Array(0,0,2,0)
args1(1).Name = "OuterBorder.LeftDistance"
args1(1).Value = 0
args1(2).Name = "OuterBorder.RightBorder"
args1(2).Value = Array(0,0,2,0)
args1(3).Name = "OuterBorder.RightDistance"
args1(3).Value = 0
args1(4).Name = "OuterBorder.TopBorder"
args1(4).Value = Array(0,0,2,0)
args1(5).Name = "OuterBorder.TopDistance"
args1(5).Value = 0
args1(6).Name = "OuterBorder.BottomBorder"
args1(6).Value = Array(0,0,2,0)
args1(7).Name = "OuterBorder.BottomDistance"
args1(7).Value = 0
args1(8).Name = "InnerBorder.Horizontal"
args1(8).Value = Array(0,0,2,0)
args1(9).Name = "InnerBorder.Vertical"
args1(9).Value = Array(0,0,2,0)
args1(10).Name = "InnerBorder.Flags"
args1(10).Value = 0
args1(11).Name = "InnerBorder.ValidFlags"
args1(11).Value = 127
args1(12).Name = "InnerBorder.DefaultDistance"
args1(12).Value = 0

dispatcher.executeDispatch(document, ".uno:SetBorderStyle", "", 0, args1())

i dont even what this program is doing and what was the outcome?

jason

16-Aug-2010 03:52

can anyone help me with this?

args1(0).Value = Array(0,0,2,0)
args1(1).Name = "OuterBorder.LeftDistance"
args1(1).Value = 0
args1(2).Name = "OuterBorder.RightBorder"
args1(2).Value = Array(0,0,2,0)
args1(3).Name = "OuterBorder.RightDistance"
args1(3).Value = 0
args1(4).Name = "OuterBorder.TopBorder"
args1(4).Value = Array(0,0,2,0)
args1(5).Name = "OuterBorder.TopDistance"
args1(5).Value = 0
args1(6).Name = "OuterBorder.BottomBorder"
args1(6).Value = Array(0,0,2,0)
args1(7).Name = "OuterBorder.BottomDistance"
args1(7).Value = 0
args1(8).Name = "InnerBorder.Horizontal"
args1(8).Value = Array(0,0,2,0)
args1(9).Name = "InnerBorder.Vertical"
args1(9).Value = Array(0,0,2,0)
args1(10).Name = "InnerBorder.Flags"
args1(10).Value = 0
args1(11).Name = "InnerBorder.ValidFlags"
args1(11).Value = 127
args1(12).Name = "InnerBorder.DefaultDistance"
args1(12).Value = 0

dispatcher.executeDispatch(document, ".uno:SetBorderStyle", "", 0, args1())

i dont even what this program is doing and what was the outcome?

bert

16-Aug-2010 09:20

Jason,

In the middle of the night...

Send code has nothing to do with Cardbox.

If you want to insert the number of (current) records as a result of a search then this your code:

editrecord
fields("NUMBER_OF_RECORDS") = Records.Count
saverecord

regards
bert

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home