9 Replies Latest reply: Feb 3, 2012 4:40 AM by Toni Kautto RSS

    Querying a field without selecting it

      his Hi, I am trying to come up with a way to localize formats and dates in the documents, so far I have the formats completed, but I still have a problem with dates.

       

      If you enter this string as a format "'WWW, MMM D, YYYY'" it will show day name, month name, day number, and year number.

       

      What I am trying to localize are both daynames and monthnames, so i have created a couple of tables to at least try to solve the month names.

       

      the tables are Languages and MonthNames

       

      qv3.png

       

      Then then the user selects a language, I can drice the rest of the date parts from the tables, the problem that I am having is that I cant seem to create a set analysis script that will allow me to "query" the actual MonthNames table and ask for the name of the month for the selected language, for the selected date, but since I dont have a month selected, I cant seem to select the Month name without actually selecting it.

       

      I am building a custom function that will accept the date and it will look internally for the selected language and do the rest.

       

      if
          
      LanguageID = FieldIndex('Locale',GetFieldSelections(Locale))
          
      and MonthID = Num(Month(Date)) 
           ,
      MonthName
           ,0
      )

       

      This goes fine, for the languageID, since I have it selected, but since I dont have the month Selected somewhere, no MonthName is returned.

       

      I am attaching a test document that describes exactly my issue, it seems to be pretty easy, but I can't seem to find the current way to pull the month name.

       

      Thanks in advance for the time

       

      -ed

        • Querying a field without selecting it
          Toni Kautto

          Have you considered the option to change the value of the MonthNames variable through a triggered action when you select a new language? This variable is used by the built in date functions and would give the right names automagically if you change the variable.

            • Querying a field without selecting it

              Hi Toni, I have done that, and for some reason, QV doenst take the new values into account.

                • Querying a field without selecting it
                  Toni Kautto

                  Yes, I see now after testing that the variable does not affect the formatting. I will check this and get back to you later this week.

                    • Querying a field without selecting it

                      Thanks a lot Toni!

                        • Re: Querying a field without selecting it
                          Toni Kautto

                          I have reported this as a bug with ID 44092, suggesting that there are three alternatives for an expected solution on chnaging the system variables.

                           

                          - the format change is reflected in function calls

                          - it should not be possible to change the system variable from the front  end

                          - system variable change can be done via function call

                           

                          I will provide an update as soon as there is any feedback from R&D. In meanwhile you can of course continue implementing a solution based on language related tables.

                           

                          ---

                           

                          Back to your initial question.

                           

                          Running the expression in a text object means that if will be calculated towards the total data set, since there are no dimensiosn to evaluate the expression towards in a text object. Considering this, what will a fieldname return as comparasment value in the IF clause? The field value will only expand in to an actual value it the current selection makes the content of the field to contain a distinct value, which you can see if you make a text box with for example the expression =LanguageID . This textbox will only display a value when there is only one possible or selected value in the field.

                           

                          My suggestion in this type of case is to split up the expression in different object so that you can follow the condition during your selections in order to ananlyse it behaviour, as you can see in the attached example there will be NULL values interfering in most scenarios. An if statement like this would be more sucessful in a chart object being calculated over suitable dimensions.

                           

                          In the example QVW you can find a set analysis test to convert a date in to the corresponding language based month name. Here you will see that the month id can be calculated sucesfully as well as the set analysis with statically set month ID. The dollar expanded month id calculation however fails due to the dollar expansion being done over the enitre datat set and not only row by row.

                           

                          A way forward on this would instead be to remodel the data a bit and link the tranlation to the data.

                           

                          First step is to join the language and month value into one table, as I do not see the purpose of keeping them separated. Notice that this does not mean that the data source need to change, it is only the way the data is storeed in the QVW that is affected.

                           

                          Secondly I will assume there is always a selected language, so I force a language selection in the application by making the langiage selected in a listbox. Now the language parameter can be ignore din the set analysis, as it is always made in the current selection.

                           

                          To get around the dollar expansion issue it is easiest to link the month ID to the table that holds the date value. So this has been done in the Sales table as a simple preceeding load. Now the MonthName can be fetched based on the current dimension in the chart.