How can you get a an empty field to count as 0 instead of 1 for :sum or :count features?
1 Like
Thanks, John. Unfortunately, this solution doesn’t really work for my situation. I need to actually get the field to count zero rather than just skip it.
Here’s a little more detail.
I have a type called Projects which includes as a field another type called Formats.
I have a number field in each of these types. The number is always set to 1 for the Projects. The number for the formats is a sum of the projects numbers (1) with a constraint for the specific format when a Project is created or revised. It’s set up this way because I have a chart on another page to show how many of each format the user has.
The isn’t empty constraint is fine for the initial creation of a project, but if someone has one of a format and changes it to a different format, the count stays at one instead of going to zero.
Any ideas on how to get around this?
So to clarify the actual field value in the database changes from 1 to 0 but the count still stays at 1?
If that’s the case could you check if the field is 0 then count those and subtract by the number of 0’s found?
I want the field value to change from 1 to 0, but it isn’t working. It’s reading that now blank field as 1 rather than zero.
For example, let’s say a new user creates a project with a format of article. The format count field under the type format would be one for that format. But if the user then changes the format to a different value, say ebook, then the format count field shows as one for both formats under the type Format, even though there is now no count that matches the article field in a sum of the projects used to calculate that field.
It only appears to be an issue going from 1 to 0. Going from 3 to 2, 2 to 1, etc. works as it should.
When you say it’s not working - does the value 0 show up in the database when you change it from 1 or does it stay blank as you mention? What’s the actual assignment your using to make that happen - maybe a screenshot would be helpful.
Another option might be to change your counting system to start from a base of 1 and go from there and remove 0 from the equation. However, that would mean a one time (not sure how painful process) to reset all your counting states.
OK. I’ll gather up some screen shots tonight in the hopes it will clarify things.
Thanks again.
OK. You’ll see that I have a fields for format and format count in the type Projects.
And here is the Formats type.
Each project that is created has a format count of 1. And then for the format count in the format type, I count the sum of the project forma counts.
Editing a project is similar. The workflows calculate the sum of the original and new formats. This is where I’m running into trouble. Instead of going from 1 to 0, it goes from one to a blank field and assigns a 1 to it, giving a false count.
Does that make sense?
Yes that makes sense.
So in your workflow where you do the edit resulting in blank field can’t you just assign it the value of 0 instead? Or is that what you currently have and it just puts a blank there instead? What does that equation look like?
How would I do that?
In your workflow where you let the user make the change - add pick make changes to a thing into the flow then you can specify format = 0 under +change another field
But it would only be 0 if it had a value of 1 previously. Wouldn’t that set the value to 0 every time someone made a change?
Hey @sean, I’m reading through this and trying to understand the bigger picture of what you’re wanting to accomplish. What’s the purpose of having the format count field in Projects if it will always be 1 and to gather the sum of all projects by Format, you do a : count for just that instead…?
First, instead of Format under Project being a type text, it should just be type Format. Then…
-
Create new Project > fill in all fields and Format now equals a Format in your database (either via search or a Format value that’s already on the page)
-
make a change to 1 Format, not a list right? It would be the result of step 1’s Format > FormatCount= do a search for projects : count where the search has the constraint “format = this format you’re modifying”
I think you’re creating too much work for yourself with a summation of a formatcount field when you can just be counting the number of Projects whose Format field (type Format) equals a given Format.
Yes or no?
Gaby | Coaching Bubble
1 Like
Thanks, Gaby. That does cut some of the fat. I’m still left with a problem, though.
This changes the count of the new format. But how do I change the count of the previous format? I thought I had it before by “changing” the data to what was already there so that I had a step to use further on in the workflow and then changing it again to what really changed (Step 2). But it appears to be just making the change right in that first step.
Any idea what could be happening?
Ah, I didn’t even consider that. Ok, so instead of making a change to 1 format, we go database-wide and make a change to all formats every time a new project is created and/or modified; that way it accounts for new and existing counts.
Make a change to a list of formats (no constraints) > count = do a search for projects whose Format is “this format” : count
Try that.
Worked like a charm, @romanmg! Thanks.
1 Like