Discussions

 View Only
  • 1.  Count Table of Display sizes by Location

    Posted 05-05-2019 13:33

    I have 2 tables: Parent Table - Building related to child - Location related to child - Workstation

    Workstation Table has a field Display Monitor Size with values 22", 24", and so on. Currently this table has more than 35,000 records

    I would like to create a table with data as below. I am struggling with how to create it. Any ideas? Please help! Thank You.



  • 2.  RE: Count Table of Display sizes by Location

    Posted 05-05-2019 13:38
    Do you mean that rather just have a summary report, you want an actual table with all the unique rows as records in the table. 


  • 3.  RE: Count Table of Display sizes by Location

    Posted 05-05-2019 13:42
    Yes. The reason is that further action needs to be done on this count that will lead to deployment.


  • 4.  RE: Count Table of Display sizes by Location

    Posted 05-05-2019 14:01
    So there are two steps.  making the table and maintaining the table.

    The making the table is easy but there is a trick.  You need to create a key field for the table in the format.

    Building Name-Department Name-Display Size.

    So  you create the table and make a field with that long name as the Key field and populate it by Creating a field in the details table with the formula


    List("-", [Building Name], [Department], [Display Size])

     and then making a summary report like you already did, except do it on that formula field.  The use the more .. button to copy to another table.

     The create a field called [Building Name-Department Name-Display Size exists?] as a formula checkbox field with a formula of true.

    Then build a relationship back to the details table and look up that field.
    [Building Name-Department Name-Display Size exists?]

    Lastly fire an Automation that says when a detail record is created and 
    [Building Name-Department Name-Display Size exists?] = not checked, then add a record to the Summary table and populate that Key field with the value in the formula field.

    You will then be able to get your total counts from a summary field on the  Relationship and they will be self maintaining.


  • 5.  RE: Count Table of Display sizes by Location

    Posted 05-06-2019 02:45
    Was able to follow along until copying summary report data to another Table. I am confused with steps after that. Which table should the [exists?] field be created in - details table or new table? How do I check if that data exists in the new table?


  • 6.  RE: Count Table of Display sizes by Location

    Posted 05-06-2019 10:39
    [exists?]  Should be in the Parent table which is the unique key table.  The look that up down to the details table.  If that is false then the lookup failed and the Parent does not exists and hence needs ot be added by the Automation.  You can also safety Summary report  of details missing parents.


  • 7.  RE: Count Table of Display sizes by Location

    Posted 05-05-2019 14:58

    Thank You very much. I will try this.


  • 8.  RE: Count Table of Display sizes by Location

    Posted 05-05-2019 15:15
    Np. Post back if you get stuck. The steps are not hard but there are a few of them to do.