7 Replies Latest reply on Jun 7, 2011 11:18 AM by AnitaWoods

    Field names in a value list

    AnitaWoods

      Title

      Field names in a value list

      Post

      I am a FM beginner, with a db with a date field called "EDD" that uses a value list that comes from a calculated field called "EDDvaluelist" (text result):

      List ( (EDD by EDC) ; (EDD by US) ; (EDD by CD) ; (EDD by LMP) )

      EDD by EDC is a date field
      EDD by US is a date field
      EDD by CD is a calculated field, date result
      EDD by LMP is a calculated field, date result

      The user can then choose one of four dates to populate EDD (date type).  The user's choice of EDD is then used in yet another date calculated field called "WGA".  The EDDvaluelist must be text result to display the four possible dates correctly, and EDD must be date type to be used in the WGA calculation.  

      What I need is for the user to be able to SEE the fieldname in the value list, so they know the specifics on which date they are choosing.  I want the value list to look like this:
      01/06/12 EDD by LMP
      01/01/12 EDD by CD
      01/11/12 EDD by US
      01/12/12 EDD by EDC

      I can get this done if EDDvaluelist is a concatenation, but when I do that, EDD must be text type to accommodate the concatenation, and thus cannot be used in the WGA date calculation.  Any ideas on how to make this work?  I keep thinking "also display values from a second field" may be useful in the value list, but how?

        • 1. Re: Field names in a value list
          Abhaya

          Hello Anita,

          I saw your question and here giving some best answer from my point of view.

          Suppose you have 2 DATE type fields Date1 and Date2.Make a constant1 selfrelationship among your current table here(test)

          test::cons1=test2::cons1(this is the self join table).

          MAke another calculation field D_valuelist that takes  the list of date fields e.g D_valuelist=list(Date1,Date2)

          Make 2 addional calculation field  like......

          List1=list(test2::Date1)

          List2=list(test2::Date2)

          Finally make the EDD field (dropdown having the valuelist from fieldD_valuelist )

          Make again 2 calculation field like

          ShowF1=If (  PatternCount (test 2::List1;test::EDD) ;GetFieldName ( Date1 ) ;"")

          ShowF2=If (PatternCount (test2::List2 ;test::EDD) ;GetFieldName ( Date2 );"")

          So first enter the date fields in Date1 and Date2 fields

          then  in the EDD field insert  the merge field ShowF1  and ShowF2 and group then.

          then when you choose any value from the EDD field by dropdown  shows its correspoding field name near by it.

           Don't afroid  to creating  more new fields  it may be bore but it 'll bring your result.

          Thanks and regards

          • 2. Re: Field names in a value list
            philmodjunk

            Are the four different dates unique to the current record or are these four values the same for all your records?

            If they're the same for all your records (or specific groups of recors), then there's a way to set up your dates in a related table that then supplies the dates for your value list.

            Assuming that the 4 dates are specific to the current record, I'd take a little bit different approach here.

            Define a calculation field set to return date like this:

            Case ( RightWords ( ValueListField ; 1 ) = "LMP" ; EDD by EDC ;
                      RightWords ( ValueListField ; 1 ) = "US" ; EDD by US ;
                      RightWords ( ValueListField ; 1 ) = "CD" ; EDD by CD ;
                      RightWords ( ValueListField ; 1 ) = "LMP" ; EDD by LMP )

            Your value list field can be your current value list, or you can set it up with your concatenation to include the dates. If you do not include the dates in your valuelist field, put the above field next to it to display the selected date. If you include the date in the value list calculation, you can hide this field from the layout, but use it whenever and wherever you need to treat the selected date as a date and not as text.

            • 3. Re: Field names in a value list
              AnitaWoods

              Thank you.  I am struggling to make either of these options work.  Atauf's answer lost me at "then  in the EDD field insert  the merge field ShowF1  and ShowF2 and group then."  Could you explain what this means?  

              PhilModJunk, these are four different dates unique to the current record.  I created a field "EDDcase" with the Case calculation (date result) below. Thank you for writing the code. I input it this way:

              EDDcase =
              Case ( RightWords ( EDDvaluelist ; 1 ) = "LMP" ; EDD by EDC ;
                        RightWords ( EDDvaluelist ; 1 ) = "US" ; EDD by US ;
                        RightWords ( EDDvaluelist ; 1 ) = "CD" ; EDD by CD ;
                        RightWords ( EDDvaluelist ; 1 ) = "LMP" ; EDD by LMP )

              where EDDvaluelist (text result)= 
              List ( (EDD by LMP) ; (EDD by US) ; (EDD by CD) ; (EDD by EDC)  )

              I couldn't get this to show anything, either by date result or text result.  Should this be a drop-down? Edit box?  When I use a drop-down with value list EDDvaluelist, it shows only the dates when I click on the dropdown, but when I select one of them, it says the field is not modifiable.

              I have tried the EDDvaluelist both as above and as a concatenation:

              List ( (EDDbyLMP) & " by LMP"; (EDDbyUS) & " by US" ; (EDDbyCD) & " by CD" ; (EDDbyEDC) & " by EDC" )

              I am lost on what to do with either of these solutions.  I will add that, honestly, I don't need the actual field names, I can use text next to the date, but I need to be able to see both the date and a description to assist the choice of the appropriate date. And I need it to be a date field, in order to utilize it in another calculation.

              • 4. Re: Field names in a value list
                philmodjunk

                I see I misread your original post. I thought your value list was List ( "EDD by LMP" ; "EDD by US" ; "EDD by CD" ; "EDD by EDC"  )

                That way, your original value list lists the text that identifies the field from which date is being selected. You'd select the value that identified the source of the date and the case function in a calculation field set to return "date" as it's return date would return the actual date that you wanted.

                I'm assuming That the text in quotes are also the actual names of the 4 date fields.

                can you use this calculation?

                List ( (EDDbyLMP) & " by LMP"; (EDDbyUS) & " by US" ; (EDDbyCD) & " by CD" ; (EDDbyEDC) & " by EDC" )

                to produce the 4 item value list that you want?

                It would take a conditional value list that returns the contents of that calculation field only for the current record in order for that to work. This would require a self join relationship. I haven't tried to describe how to do that as I understood that you already had your four item value list working for you, but if you don't know how to do that let me know.

                • 5. Re: Field names in a value list
                  AnitaWoods

                  Yes, the concatenated 4 item value list works when the calculation result is text, and comes up with the dates for the related record.  And yes, the text in quotes are the actual names of the 4 date fields.  I've gone back and forth with them having spaces/no spaces.  I keep thinking there has to be a way to "show a second field" with the value list dropdown, showing the field where each date came from, but storing the date, not the date with the text description.  I have something similar with each record's pkContactID from my Contacts table.  The ContactID in each related table stores the pkContactID, but I can show the name field as the second field, but still have it store the autonumber.  

                  Flashbulb: what about another calculated field, date result, that simply strips back out the text from the EDD?  Left(EDD ; 10) that stores only the date, dumping the text? Then using *that* field in the WGA calculation?  Put the concatenated text at the beginning of the field, then strip back out the first ten characters? (using two spaces with CD & US)?

                  • 6. Re: Field names in a value list
                    philmodjunk

                    The extra field option can work, but it would require a related table of your dates instead of 4 separate fields. That might be a very good way to go here, if you can get the calculated dates to work in this related table.

                    Your table would have at least three fields:

                    ForeignKey (links to current record in your original table)
                    Date
                    Date Source (Text. with EDC, LMP, US, or CD stored in it.)

                    Then you can set up a conditional value list listing DateSource and Date as the two columns of values in your value list. The trick would be to get the calculated dates to work for you in this related table. (Whichever field you choose as field 1 has to be a stored, indexed field and calculation fields that refer to values from other tables cannot be stored.)

                     

                    • 7. Re: Field names in a value list
                      AnitaWoods

                      While it doesn't list field names in a value list, creating another field that strips the text back out of the EDD from the value list has solved my problem.  I simply made the value list field a concatenation of all four dates with text descriptions:

                      List ( (EDD by LMP) & " by LMP"; (EDD by US) & " by US"; (EDD by CD) & " by CD"; (EDD by EDC) & " by EDC")

                      and then created another field called EDDforWGA in the same table that strips it back out, to be used in the WGA calculation:

                      Left ( EDD ; 10)

                      I didn't need any kind of related table, or anymore fields other than this one field.  I am a bit puzzled though, because now my WGA calculation, in another related table, isn't working correctly.  I feel like my husband does when he is repairing something around the house.  One small chore always creates another.