Use of parentheses in a text string

[Anonymous]

Hi all,

I'm looking for a solution to what I had thought wouldn't be such a challenge. Basically, I wish to use a TextLabel to show how many rows are left in a table of data, based on how many rows match a specific date (the date being whatever it is, when the user views the app). In and of itself, that is not an issue:

CountIf(Datatable, "date" = Today())

However, and this where my troubles start, I wish to enclose that number of remaining rows in parentheses, i.e. if there are seven rows left, I want the label to read "(7)" (without the quotation marks).

I've wondered if I should and have tried to use the Concatenate, or Text functions but neither have worked. I'm sure it's possible, but I'm just fussing it up. Can I string multiple formulas together with ";" or do I need to use Concatenate / Concat with Text- and Countif-function nested inside it?

Oh great internet, lend me your ears (and help, please)!

[gabibalaban · Accepted solution]

hi @Anonymous ,

Do you mean you need this in your Text property of a Label ? 

"(" & CountIf(Datatable, "date" = Today()) & ")"

[Anonymous]

Yes, exactly!

I feel like I tried that model, but with concat() instead of using the ampersand. But that didn't take, so I figured it wouldn't work and climbed deeper into a jungle of unnecessarily complicated solutions. 

I feel silly now, seeing how simple a working solution is.

Thank you!

[Anonymous]

On a related note:

If(
   CountIf(
     Datatable, "date" = Today()) > 0,                              // If result is more than zero
     "(" & (CountIf(Datatable, "date" = Today())) & ")",            // then show result in parentheses
     " "                                                            // if not, show nothing (space)
  ) 

This produces an error saying "Incompatible type. We can't evaluate your formula because the values being compared in the formula aren't the same type". This is an attempt to remove or make the label invisible if the result is zero. Any ideas what I'm doing wrong here?

[gabibalaban]

@Anonymous  if date is a column in Datatable , please remove the doble-quotes or use single quote.

If(
   CountIf(
     Datatable, date = Today()) > 0,                              // If result is more than zero
     "(" & (CountIf(Datatable, date = Today())) & ")",            // then show result in parentheses
     " "                                                         // if not, show nothing (space)
  )

Or

If(
   CountIf(
     Datatable, 'date' = Today()) > 0,                              // If result is more than zero
     "(" & (CountIf(Datatable, 'date' = Today())) & ")",            // then show result in parentheses
     " "                                                         // if not, show nothing (space)
  )

[Anonymous]

Thanks.

I tried that just now. The result was a different warning: "Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "CountIf" operation is not supported by this connector".

None if the text in the formula is highlighted, though, and the formula produces no results, even though there is one entry in Datatable (out of a total of 10 rows), that should match the "true" criteria of the If-function - i.e it should produce a "(1)". Here's what it says now: 

If(
    (CountIf(
        Datatable,
        'Date' = Today()
    )) > 0,
    "(" & (CountIf(
        Datatable,
        'Date' = Today()
    )) & ")",
    " "
)

I wonder if there's something messing up, with putting a non-delegatable function (countif) inside a delegatable funtion (If)?

[gabibalaban · Accepted solution]

@Anonymous ,

Is a Sharepoint list your data source ? Can you tell me the column type for Date Column, is a DateTime ? 

If so, to avoid the delegation warning and get the right result use this (don't laugh 😂)

CountRows(LookUp(GroupBy(LookUpTestList,"Created","Result"),Created<DateAdd(Today(),1,Days) && Created>=Today()).Result)

I let you the pleasure to discover the logic of this magic formula.

[Anonymous]

The data is stored in a Sharepoint list, yes. And the column in question is formatted as DateTime, yes.

I'm struggling to understand what your purposed magic formula does? I gather it tried to single out a column from the table, for the purpose of counting how many rows are in it. But, I'll be honest, I don't understand what's going on in the latter half of the LookUp-function 🙂
If I try to match it up to my test table, I think it would look like this?

CountRows(
         LookUp(GroupBy(Datatable,"Date","Result"),
         Date<DateAdd(Today(),1,Days) 
         
         && Date>=Today()).Result
         )

Or am I mistaken?

[Anonymous · Accepted solution]

@gabibalaban I think I've finally worked out how the magic of your formula works! It is quite neat.

I've ended up using a different formula to achieve my desired result:

"IDAG " & If(
    CountRows(
        Filter(
            [@Transportblanket], Dato=Today()))=0,
            " ",
            "(" & Text(
                CountRows(
                    Filter(
                        [@Transportblanket], Dato=Today()))) &")"
             )

A generic version would be something like this:

"Today's Orders " & If(
    CountRows(
        Filter(
            [@Datasource], Dato=Today()))=0,
            " ",
            "(" & Text(
                CountRows(
                    Filter(
                        [@Datasource], Dato=Today()))) &")"
             )

This formula is the quickest way to get the result (loading-wise) and it simply removes the "parenthesis-value-parenthesis" part if the value of the formula is less than 1. Essentially this helps users determine quickly whether or how many orders are due for the current day.