Dynamic Words Based on Selection

[#1]

Hi everybody!

Thanks for taking the time to read this post. I need to make the words in a text box change based on the user's selection. I have a master calendar that links fiscal year, fiscal quarter, and fiscal month with my data. I need the words to represent the selection. So if a user selects 2014, the words need to say 2014. If the user selects March and 2014, the words need to say Mar-14 (like how it's formatted in my FiscalMonthYear column in my master calendar), and so on and so forth. I've tried these possibilities and have gotten the following results:

//displays -1 =if(([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), (FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), '-')

// displays - =GetFieldSelections(FiscalMonthYear)

// displays - =if(GetFieldSelections(FiscalMonthYear), FiscalMonthYear)

// displays -1 =[Fiscal Year] or FiscalMonth or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear

//displays -1 =if([Fiscal Year], [Fiscal Year]) or if(FiscalMonthYear, FiscalMonthYear)

// displays -1 =if(isnull(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear),'Fiscal Period', FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarterYear or FiscalQuarter)

//= displays - GetFieldSelections(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear)

//displays -1 =if([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, '-')

// displays year =if([Fiscal Year] or FiscalMonthYear, [Fiscal Year] , FiscalMonthYear)

Thanks in advance for your help!

[swuehl]

If all your fields have a numeric representation, you can use something like

=alt(Date, YearMonth, Year)

It's important that you order the arguments finest granularity first, largest granularity last, so it will display Date when a single date is available / selected, then check for single YearMonth, then check for single Year.

If you have non-numeric values, same would apply for nested if() statements:

=if( len(Date), Date,

     if( len(YearMonth), YearMonth,

          if(len(Year), Year)

    ))

[#3]

That definitely got me closer! Thank you! Now I have:

=if(len(FiscalMonth), FiscalMonth,

     if(len(FiscalQuarter), FiscalQuarter,

      if( len([Fiscal Year]), [Fiscal Year],

      if( len(FiscalMonthYear), FiscalMonthYear,

      if( len(FiscalQuarterYear), FiscalQuarterYear,)))))

But it will only show one thing at a time even if multiple selections are made. So if I select 2014, it will display 2014. If I select January, it will display Jan. But if I select both, it will just display Jan. What am I doing wrong? Thanks again!

[swuehl · Accepted solution]

It may be just a question of how you prioritize the fields you want to show:

In my point of view, FiscalMonthYear is more granular / specific than a Month or Year:

=if( len(FiscalMonthYear), FiscalMonthYear,

     if( len(FiscalQuarterYear), FiscalQuarterYear,

         if( len(FiscalMonth), FiscalMonth,

            if(len(FiscalQuarter), FiscalQuarter,

              if( len([Fiscal Year]), [Fiscal Year])

     ))))

I am a little unsure how to get the quarters in sequence, so you may want to rotate things a little.

[#5]

That worked! Final question: how do I add an if no selection, display Fiscal Period? I tried writing:

=if(isnull('Fiscal Period'),

       if(len(FiscalMonthYear), FiscalMonthYear,

           if(len(FiscalQuarterYear), FiscalQuarterYear,

             if(len(FiscalMonth), FiscalMonth,

                 if(len(FiscalQuarter), FiscalQuarter,

                   if(len([Fiscal Year]), [Fiscal Year]))))))

[swuehl]

If there is no selection, is there only one single possible value for Fiscal Period?

If there are multiple values possible, which one should be shown?

[#7]

Sorry, I think it was unclear. Right now if there is no selection, it displays a hyphen. I want the words "Fiscal Period" to be there instead of - . Any ideas?

[swuehl]

=if( len(FiscalMonthYear), FiscalMonthYear,

     if( len(FiscalQuarterYear), FiscalQuarterYear,

         if( len(FiscalMonth), FiscalMonth,

            if(len(FiscalQuarter), FiscalQuarter,

              if( len([Fiscal Year]), [Fiscal Year], 'Fiscal Period' )                

     ))))

[#9]

For some reason it displays 2014 instead of Fiscal Period. I don't know why as that doesn't make any sense. I triple-checked to make sure all the selections were reset. Any idea why? Thank you so much for your help!

[swuehl]

The epression does not check explicitely for selected values, just for single possible values.

How many possible FY do you have in your app? Only a single one, 2014?