After setting up a workbook based on the treat-tracker csv file, I used the Summarize Table wizard to create a Summary_Who table, and then toggled "Add aggregate columns" to sum-up a column using the "Count" column. Attached is a screenshot of the resulting table, with the formula showing for the "Sum Count" column (first row -- but all the rows in this column contain the same formula). The formula appears to be failing (since 0 is shown for each row in the column -- which is the error message).
Here is the formula as shown: ==IFERROR(SUM([Related Treats][Count]), 0)
The formula was created by the wizard. It looks okay to me (but I'm not a programmer, and still just learning Honeycode, so I don't have much confidence in my analysis and debugging skills). Can anyone tell me what's wrong with the formula shown, and what the proper formula should be to show, in each row, the sum of the counts for each dog? Thanks. Fred
Thanks for the question @Fred-7fc8 , if it's possible to share, and the CSV doesn't contain any personal information, can you share a screenshot of the treat tracker table (the table you used to build the summary)? If that is not possible, can you describe what the Count column in the original table contains, and what you're attempting to do with it?
Two screenshots were required to include the entire "Treats" table for my treat tracker workbook. Here they are....
Why are the numbers in the Count column in rows 31 to 34 (the bottom four rows of the table) formatted differently than in the rows above? What's the semantic meaning of this difference, and might it have something to do with the formula error?
Thanks for sharing @Fred-7fc8 this was helpful! It appears that the numbers in the screenshot under the Count column in blue are rowlinks (Intro to Rowlinks & Picklists). Is it possible that you ran the Picklist wizard and chose to convert this column? In any case, one cannot "count" a rowlink directly: conceptually rowlinks hold the entire content of the row in a cell and not just what is displayed. Instead you want numbers in the column so they can be SUMed. Here's something I would try to see if it fixes this issue:
- Delete the content in the
count column
- Format the column as
number ( check out How to Add & Format Columns )
- Add back numbers either by typing them in manually or by copying and pasting from the CSV.
- Check if the summary table shows updated counts.
Let us know if this solves your issue!
The table appears to have gotten corrupted. Because I have been experimenting with wizard-driven and manual changes to table, it's probably impossible to reconstruct how it got to this point, but it's in a bad state. So I'm just going to delete the entire application, and start over. Thanks.
@Fred-7fc8 and @RajeshGoli, I had a similar issue and did as @RajeshGoli suggested in the June 30 comment. In fact I had mixed Numbers and Picklist formatted numbers in that column, so I cleared all the values, re-formatted the entire column as Number, and hand-typed into the column all Number (and some blank) entries. After that aggregations worked fine.
However my app is a movie database where the user chooses a movie rating from 1-5. In order to enforce entry of only those values, I formatted the Data Cell in the entry form to be a Rowlink to a Table of the Numbers (formatted as Numbers) from 1 through 5. This created the desired drop-down where the user can pick 1 through 5 only.
However even though both the origin Table (Ranks_List) and the destination Table (CustomerInput[Rating] are formatted as Number, when a user enters a new rating using the picklist on the entry form, it enters a Rowlink formatted entry in the CustomerInput[Rating] Table, resulting in, once again, a mix of Number and Rowlink formatted entries in the Rating field. This breaks aggregations in the Summary table.
Do either of you have suggestions how the user can pick from a dropdown list of numbers, and have that choice actually be entered in the destination table in the Number format instead of Rowlink?
Thanks!
Hi @scott-merry thanks for your question, and welcome to the forum. I'm glad to know that the solution worked for you. One way I can think of fixing your new issue is by using what is sometimes called as "dereferencing" or "Data links" in this article: FindRow: A Better VLookup.
I'm going to assume that your "Ratings" table looks something like this:
In the automation that runs when you click the "Done" button, you likely add a new row to the CustomerInput table, and take the value from =$[Rating] and write it to the =[Rating] column. Instead of doing that, I would change that to be something like take data from =$[Rating][Rating], where the first Rating is the name of the variable data cell and the second Rating is the column you are looking up from that rowlink that links to the Ratings table, and write it to =[Rating] column in the CustomerInput table. That will ensure that you are always writing a number in the Rating column of CustomerInput table and not a rowlink to the Ratings table. This will also ensure that your aggregations will continue to work.
Let us know if this solution works for you!
Hi @RajeshGoli appreciate your thoughtful reply, which ultimately did work.
However when I implemented it, the below screenshot warnings / errors popped up. This caused me to spend quite some time trying to figure out where I had gone wrong. Turns out it actually functions just fine like this, despite the scary messages.
Can you tell me if there is some problem described by these messages that I'm not aware of?
Hi @scott-merry thanks for sharing and sorry you had this experience. You might sometimes see errors in app builder that may not actually occur in Runtime as you just witnessed. This is because App Builder is trying to evaluate formulae optimistically for the first row of data. But in this case, the $[Rating] variable data cell may have no data. In that scenario, your expression =$[Rating][Ranks] evaluates to #VALUE!. However, when you actually have a rowlink in the $[Rating] variable data cell, as it happened when you ran the app, your expression evaluates to a number without an error.
Incidentally, this will also be true if, as an app user, you submit the form without selecting anything in the Rating drop down. You might want to use the pattern in Validation to input filed to ensure that no app user can submit the form with an empty Rating!
Hope this helps. Happy Honeycoding!
