Forum Discussion

ReneeHansen1's avatar
ReneeHansen1
Qrew Cadet
5 months ago

List fields based on checkbox

Hi, 
I need a formula field that will list some Years if the corresponding Year checkbox is checked.

I'm not sure if this screenshot size is ok or not - it looks super tiny when pasting it.

In the field "Funding Years", if the corresponding Year has a checkbox: First year Ask is checked, then List the First Year in the Funding Years. 

If ANY combination of those "Year ask" checkboxes are checked, then I want to list that Year in the "funding years".

I've tried all the formulas I can try, I keep getting a mismatch of boolean and number errors, or all of the years populate.

My formula right now is:

var bool one = [First Year Ask] = true;
var bool two = [Second Year Ask] = true;
var bool three = [Third Year Ask] = true;
var bool four = [Fourth Year Ask] = true;

If(
$one or $two or $three or $four,

List(", ", ToText([Year 1]), ToText([Year 2]), ToText([Year 3]),ToText([Year 4])))

I tried putting "or" in there, but it doesn't seem to take it. 

  • Hi - I still need help if anyone has the answer.


    Here's a better screenshot. 
    The funding years are 2025, and 2026. I need a formula that reads "First year ask is Yes, return 2025 from the field [First Year], if "Second year ask is Yes, return 2026 from the [Second Year] field.

    My Numberic formula is only returning 2025, and my Text formula is returning all 5 years.
    Numeric Formula:
    var bool yone= [First Year Ask]=true;
    var bool ytwo= [First Year Ask]=true;
    var bool ythree= [First Year Ask]=true;
    var bool yfour= [First Year Ask]=true;
    var bool yfive= [First Year Ask]=true;

    If($yone,[Year 1],
    $ytwo,[Year 2],
    $ythree,[Year 3],
    $yfour,[Year 4],
    $yfive,[Year 5])

    My Text formula:
    var bool yone= [First Year Ask]=true;
    var bool ytwo= [First Year Ask]=true;
    var bool ythree= [First Year Ask]=true;
    var bool yfour= [First Year Ask]=true;
    var bool yfive= [First Year Ask]=true;

    List(",",ToText([Year 1]),ToText([Year 2]),ToText([Year 3]),ToText([Year 4]),ToText([Year 5]))

    The only reason i have two is because I'm trying to figure out which is the right way.

    Thanks!

    Renee

  • Is the ultimate goal to have a list of years IF that year ask button is checked? If so:

    var text FirstYear = If([First Year Ask]=true, [First Year]);
    var text SecondYear = If([Second Year Ask]=true, [Second Year]);
    var text ThirdYear = If([Third Year Ask]=true, [Third Year]);
    var text FourthYear = If([Fourth Year Ask]=true, [Fourth Year]);
    var text FifthYear = If([Fifth Year Ask]=true, [Fifth Year]);

    List(",", $FirstYear,$SecondYear,$ThirdYear,$FourthYear,$FifthYear)

    • ReneeHansen1's avatar
      ReneeHansen1
      Qrew Cadet

      Yes, the ultimate goal is to have ONLY THE YEARS FOR FUNDING listed if that ask year is checked.

      I put in that formula, but I'm getting an an error that says expecting text but found number. the "first year" et al fields are numeric.  I tried wrapping in in To Text but that didn't work.
      should I change my year fields that are numeric to text just for this? i don't think it'll mess anything.

      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander

        Did you try this?

        var text FirstYear = If([First Year Ask]=true, ToText([First Year]));
        var text SecondYear = If([Second Year Ask]=true, ToText([Second Year]));
        var text ThirdYear = If([Third Year Ask]=true, ToText([Third Year]));
        var text FourthYear = If([Fourth Year Ask]=true, ToText([Fourth Year]));
        var text FifthYear = If([Fifth Year Ask]=true, ToText([Fifth Year]));

        List(",", $FirstYear,$SecondYear,$ThirdYear,$FourthYear,$FifthYear)