6 Replies Latest reply on Jan 25, 2015 8:36 AM by fmpro_novice

# Count Letters across fields

### Title

Count Letters across fields

### Post

Would someone please create a demo file for me to show me how this would work?

Ideally, the structure would be something I can add to my existing data base - so I would use calcs instead of new relationships.

As shown in the attachment, I simply need to count the number of A's and the number of B's in the fields. Each A equals 1 and each B equals 1. The dash marks equal 1/2 and would be added to the total count of A's and B's.

My example shows (8) A's and 5 dashes, for a total of 10.5. The number of B's is (5) and 5 dashes, for a total of 7.5. Each field represents something unique, so I need the fields to stay separate. Then, the total would be a field. I also could possibly make one field for the count of A's (plus dashes) and one field for the count of B's (plus dashes) - whichever way is easiest.

Thank you!

• ###### 1. Re: Count Letters across fields

How do you tell whether a dash adds to the total for B instead of A?

This would be easier to tabulate if each field shown was the same field in a set of related records. Please note that a set of related records can be displayed horizontally as well as the more typical vertical orientation inside a single portal.

• ###### 2. Re: Count Letters across fields

The dashes add equally to both the A's and the B's. As in the example you will see that when you total the number of A's plus the dashes (each dash equals 1/2) you get 10.5. When you add the B's plus the dashes, you get 7.5.

• ###### 3. Re: Count Letters across fields

Will it work for you to use one related record for each value of A, B or - ?

• ###### 4. Re: Count Letters across fields

As long as I am able to incorporate it into my existing data base, I would think that would be okay.

• ###### 5. Re: Count Letters across fields

Well I can't tell if you are able to "incorporate it into your existing database" or not...

If you have FileMaker 12 or newer, the executeSQL() function can count all records where the field is A, all where the field is "-" and all the B's related to the current record on your layout.

Without executeSQL(), you can either set up 3 different relationships to different table occurrences of this new related table of Values or a single relationship with two one row filtered portals with a summary field in the portal row.

Or you can set up three calculation fields in the related table:

cAflag:
Field = "A"

cBflag:
Field = "B"

cHyphenFlag:
(Field = "-" ) / 2