Count Specific Records

[Mikch]

Hi,

 

This is probably an easy one but I've been looking for an answer for two hours now and ca''t seem to come up with an answer. I'd like to have a field on my main layout that shows a count of records that have the criteria of "Flag" in the a field. I'm trying this with a calculated field but I am not sure what the correct syntax is to could all records with the work "Flag" in the field Flag. In excel it would be a CountIf statement.

 

 

Thanks in advance,

 

Michael

[AHunter3]

Create this calc field:

 

FlaggedSerial = Case (YourField = "Flag", 1)

 

 

Now, do youi want the total number of flagged recs in the current found set, or in the table regardless of whether found or not found?

 

in the found set: create this summary field:

SumFlagged, Count of FlaggedSerial

 

in the table regardless:

 

create "X" relationship between any two fields of that table and itself; call the resulting new table occurrence UniversalSelfJoin; then create this calc field "TtlFlagged": Count (UniversalSelfJoin::FlaggedSerial)

[Julia D.]

In addition to counting portal records that contain a specific value (i.e. "Flag"), is it possible to calculate the order number of a given portal record within this count:

 

Flagged 1 of 3

Flagged 2 of 3

Flagged 3 of 3

Unflagged 1 of 2

Unflagged 2 of 2

 

I tried Get(RecordNumber) but the result is:

 

Flagged 1 of 3

Flagged 2 of 3

Flagged 3 of 3

Unflagged 4 of 2

Unflagged 5 of 2

 

 

Thanks for your help,

Julia

[aaa]

Hi, Julia.

I think will be better have Flagged and Unflagged records in different portals.

But if you want it have in one portal i will make example.

[aaa]

Or use calculation

Case(Flagged,Get(recordnumber),Get(recordnumber)-TtlFlagged)

Sure your portal records must be sorted on Flagged and Unflagged records.

[Ender]

Create this calc field:

 

FlaggedSerial = Case (YourField = "Flag", 1)

 

 

Now, do youi want the total number of flagged recs in the current found set, or in the table regardless of whether found or not found?

 

in the found set: create this summary field:

SumFlagged, Count of FlaggedSerial

 

in the table regardless:

 

create "X" relationship between any two fields of that table and itself; call the resulting new table occurrence UniversalSelfJoin; then create this calc field "TtlFlagged": Count (UniversalSelfJoin::FlaggedSerial)

Though we have little to go on in the original post or Julia's follow up about the all the possible values of the field being counted, I'd recommend using filters on the parent side of a relationship rather than a calculated flag field to indicate which records to count. Even if there's only two possible values, i think it's better to add global filters and self-joins rather than stored calcs for this purpose.

 

For example, to get a count of each value, as in Julia's portal, I'd add a self-join relationship on the foreign key (the ParentID that the portal uses) and the StatusField (what you're putting Flagged and Unflagged in):

 

Selfjoin=

ParentID = ParentID

AND Status = Status

 

Now an aggregate Count() on that TO's primary key will give you a Total Count by Status for each change in Status, limiting the counts to the subset of records in the portal:

 

Total Count by Status (calculation, number result) = Count(Selfjoin::RecordID)

[Julia D.]

Thank you very much for replying to my question.

 

I’m sorry that my question was unclear. Let me explain in more detail.

 

I have a parent table (Artworks) and a related table (Images).

 

There are two types of images that can be related to an artwork: views and components.

 

I have a filtered portal on the main Artwork layout controlled by a radio button set with the following options:

 

Views

Components

Views and Components

 

Since there can be hundreds of views and /or components or just a few (depending on the artwork), I’d like to display not only the total number of related views or components but the number of the current related record out of the total count.

 

The following calc field works when “Views” or “Components” alone are selected:

 

Case (

 

Artwork::g Type = "Views"; Type & " " & Get(RecordNumber) & " of " & Get(FoundCount);

 

Artwork::g Type = "Components"; Type & " " & Get(RecordNumber) & " of " & Get(FoundCount);

 

Artwork::g Type = "Views and Components" and Type = "View"; Type & " " & Get (RecordNumber) & " of " & Count Views;

 

Artwork::g Type = "Views and Components" and Type = "Component"; Type & " " & Get(RecordNumber) & " of " & Count Components)*

 

 

The result is:

 

View 1 of 3

View 2 of 3

View 3 of 3

 

or

 

Component 1 of 2

Component 2 of 2

 

When “Views and Components” is selected however, the result is:

 

View 1 of 3

View 2 of 3

View 3 of 3

Component 4 of 2

Component 5 of 2

 

I originally set up the database with views and components in separate tables but I prefer to use only one table so that it’s easy to browse and print out all related images of an artwork.

 

Thanks for your help and I’m sorry again that my first question was so vague.

 

Best wishes, Julia

 

*Count Views and Count Components are summary fields set up as instructed in the earlier part of this thread. g Type is the global field used for the radio button set.

[aaa]

Hello Julia.

Try such.

Case (

 

Artwork::g Type = "Views"; Type & " " & Get(RecordNumber) & " of " & Get(FoundCount);

 

Artwork::g Type = "Components"; Type & " " & Get(RecordNumber) & " of " & Get(FoundCount);

 

Artwork::g Type = "Views and Components" and Type = "View"; Type & " " & Get (RecordNumber) & " of " & Count Views;

 

Artwork::g Type = "Views and Components" and Type = "Component"; Type & " " & Get(RecordNumber)-Count Views & " of " & Count Components)*

 

 

But your portal must be sorted such that first rows must be Views and after thus rows - Components.

If it is necessary i can create example.

[Julia D.]

Thanks, it worked perfectly!

 

Best, Julia