Alpha DevCon 2018
Results 1 to 5 of 5

Thread: Calculated Field in Browse Table Showing Field Counts

  1. #1
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default 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. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,011

    Default 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))
    There can be only one.

  3. #3
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default 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. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,011

    Default 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))

    reads as

    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.
    Last edited by Stan Mathews; 10-04-2017 at 05:01 PM.
    There can be only one.

  5. #5
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default 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.

Similar Threads

  1. Calculated field in a browse table
    By Judith in forum Alpha Five Version 11 - Desktop Applications
    Replies: 4
    Last Post: 09-23-2017, 11:05 AM
  2. Calculated field and groups in a browse table
    By Judith in forum Alpha Five Version 11 - Desktop Applications
    Replies: 3
    Last Post: 07-24-2017, 10:57 AM
  3. Calculated field in table - field rules - to update existing records when saved?
    By WendyWelton in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 01-18-2012, 07:18 PM
  4. Replies: 1
    Last Post: 08-15-2010, 11:06 PM
  5. Assign calculated field on form to table field
    By michaelklein in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 01-17-2010, 03:02 PM

Bookmarks

Posting Permissions

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