# Thread: Calculated Field in Browse Table Showing Field Counts

1. ## Calculated Field in Browse Table Showing Field Counts

I am using A5 V10.5. I know that is old, but I have a good reason for using it. However, I have not done any real serious DB creating for a while and am out of practice.
I have created a browse table for a table I created and am trying to create a calculated field showing the number of duplicates in a field in that table.
The table looks like this:

Part Number Serial Number PO Number
5A/250VP 123456 207544
5A/250VP 654321 207544
CON-6310 456789 207669
CON-6310 987654 207669
CON-6310 123654 207669

It is a very small table of part numbers with associated serial numbers. Each part/serial number combo is unique. When part/serial number is used (referenced from another table) that unique combo is pulled from this table then posted to a history table and deleted from this table. As you can see from the example above, part numbers can be listed several times. I know this table structure does not follow First Normal Form, but there is a reason for that. There are actually only 14 part numbers currently in the database, but each part number has several serial numbers, so each is listed as a separate record. There are currently 81 records in the table. It is not likely the number of part numbers will increase, but the number of part/serial number combos could increase as new parts are purchased and added to the inventory.
What I want to do, as mentioned previously, is create a calculated field in the custom browse table to count the number of each duplicate field value in the table. That should give me a total quantity of each part.
I have searched through the forum and cannot find anything that will help me with this. I know this should be pretty easy but, as I said, I am way out of practice and am just spinning my wheels. Can anyone get me going in the right direction?

2. ## Re: Calculated Field in Browse Table Showing Field Counts

For a small table,

dup = tablecount("your_table_name","field_name_to_count = "+s_quote(field_name_to_count))

3. ## Re: Calculated Field in Browse Table Showing Field Counts

Thanks Stan. Hmmm. When I enter the expression you gave me in the browse table's calculated field Expression Builder, tablecount("sfpart_serial_inv","partnum = "+s_quote(partnum)), it equates to a valid expression with a result of "4." That could be that the first four records in the table have the same part number (partnum) of "5A/250VP." If I use the > key to tab through the rest of the records, the result remains at "4." Is there a way I can count the records based on the field value of partnum?

4. ## Re: Calculated Field in Browse Table Showing Field Counts

The expression does that. I don't know what you are seeing in the expression builder but it should work correctly in the browse.

tablecount("sfpart_serial_inv","partnum = "+s_quote(partnum))

count the partnum field in the sfpart_serial_inv table where the value in the partnum field is equal to the current value of the partnum field. Thus it changes for each partnum.

5. ## Re: Calculated Field in Browse Table Showing Field Counts

Thank you so much Stan. It does indeed work. I could not understand, at first, why I could only see the count for parts that were less than ten. Then in the Column Properties>Format Tab I found the Display Format width was set to 1. Du-u-uh! LOL
Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•