10 Replies Latest reply on Jul 25, 2011 10:33 PM by trunkdog

    IfCell IsEmpty GetData from AnotherCell

    trunkdog

      Title

      IfCell IsEmpty GetData from AnotherCell

      Post

      Hello,

      I Need an "If Cell is Empty Get Data from Another Cell" call.

      I have an imported database with fields for:
      cellph, homeph and workph
      I would like to populate all empty cellph records with:
      homeph or workph else cellph

      Case (
      IsEmpty ( cellph & homeph ) ; workph ;
      IsEmpty ( cellph ) ; homeph ;
      cellph
      )

      Since I do not have a clue how to populate an existing field with data from another field I created a place holder field named holdph and set its type to Calc with the above syntax. Both homeph and workph have data. When the Calc is executed holdph is populated with "empty strings". If I set the default to a field with data say dataph, then holdph is populated. All the involved fields are text fields.

      I am admittedly new to FileMaker and have done web searchs and RTFM'ed. Could someone tell me what pew I should be looking in, Calc, isEmpty, Scripting, Relations, Tables etc? I have a way of making the simplest of task burdensome. Give me a few months, I'll be a power user! Ya gotta start somewhere…

      TIA - Barry Cool

      FMPA-11.0v2

        • 1. Re: IfCell IsEmpty GetData from AnotherCell
          trunkdog

          Per robdownunder:
          Setting data in Field2, based on selection on Field1 drop list.

          I'' do that very thing… againFoot in mouth

          Thx - Barry

          • 2. Re: IfCell IsEmpty GetData from AnotherCell
            philmodjunk

            The syntax of your case statement is correct. Thus, the issue must lie with either your data and/or the structure of your table.

            Looking at this statement, I wonder about that structure:

            "I have an imported database with fields for:
            cellph, homeph and workph
            I would like to populate all empty cellph records with:
            homeph or workph else cellph"

            Is cellph a Field or a Record? (It shouldn't be both here).

            Are cellph, homeph and workph all separate fields in the same record? (An alternative structure is to use a table of two fields: PhoneType and Number and then each phone number is a separate record--which allows for greater flexibility as someone could have two cellphones--one issued by an employer and one for personal use.)

            • 3. Re: IfCell IsEmpty GetData from AnotherCell
              philmodjunk

              Also, make sure that the cellph and homeph records are truly empty. If they contain invisible characters such as a space or a return, they will look empty, but you'd get the results you describe.

              • 4. Re: IfCell IsEmpty GetData from AnotherCell
                trunkdog

                Phil,

                I appreciate your prompt response; please see my comments:

                cellph is a field not both
                cellph, homeph and workph are all separate fields
                I understand and appreciate your suggestion for use of a Table in this instance.
                Actually I am looking for a generic answer to:
                IfCell IsEmpty GetData from AnotherCell
                as I really don't want / need the extra or container field, I want to populate an existing field and do not know how. Embarassed

                How can I confirm fields are "truly empty"? Should I try something like "isBlankSpace"?
                All fields have data in some records, but not all records have data in all fields. My first intent was to see what fields had "empty records". I first tried filtering by  =  with no records found. Long story short, I ended up using  " " . To me that looks like a search on a blank space. I also tried  *  and   =*
                Man, quoting syntax drives me nuts!


                Thank you kindly,
                Barry

                PS - It this proper protocol for a reply; posting an "answer"?

                • 5. Re: IfCell IsEmpty GetData from AnotherCell
                  philmodjunk

                  Yes, this is proper protocol. Post a Answer is both howlingly bad grammar and also would be better labeled as "post a reply".

                  If searching for records by putting = in a field returns no records, then there is data in the field.

                  You can make a quick examination of this field by clicking into a seemingly empty field and then double clicking it to to see what text is selected. (The invisible text will still be invisible, but you will be able to see how many such characters are highlighted when you do this.)

                  You can define a calculation field that use Char to return the character code for this character so that you can tell if it's a space or other character.

                  If it is one or more spaces, you might try this expression:

                  Let ( [ c = Trim ( cellph ) ; h = Trim ( homeph ) ] ;
                         Case (
                                 IsEmpty ( c & h ) ; Trim ( workph ) ;
                                 IsEmpty ( c ) ; h ;
                                 cellph
                                 ) //case
                       )//Let

                  Trim will remove all trailing/leading spaces from the data in this field so this will only work in cases where the text in the field is only space characters.

                  • 6. Re: IfCell IsEmpty GetData from AnotherCell
                    trunkdog

                    Yes… "howlingly bad"! Sounds rather like a British Rock band from the 60's. Cool Your suggested syntax worked. I actually cut/pasted the invisible char in Text Wrangler, used show spaces and the result was ◊; the unfilled diamond. Selecting the field then dbl-clicking would highlight the entire field. Anyway would you be so kind as to tell me if I can populate an existing field with data in some of the records from data from another field. Embarassed I am into the manuals and videos to tame this beast.

                    My syntax:

                    Let ( [ w = Trim ( workstat ) ; h = Trim ( homestat ) ; l = Trim ( licnstat ) ] ;
                           Case (
                                   IsEmpty ( w & h & l) ; genstat ;
                                   IsEmpty ( w & h) ; l ;
                                   IsEmpty ( w ) ; h ;
                                   genstat
                                   ) //case
                         )//Let

                    I used the field "genstat" because I have instances where all three fields ( w , h and l ) are vacant. The genstat field is always occupied.

                    Thx again - Barry

                    • 7. Re: IfCell IsEmpty GetData from AnotherCell
                      philmodjunk

                      Sorry, but I thought this calculation answered that question for you.

                      Isn't that what this calculation does?

                      How does what you want differ from using this calculation in a calculation field or as an auto-enter calculation?

                      (The second option will enter a value into a data field when the record is created or imported, but if "Do not replace existing value..." is left selected, you can then edit the data entered by the calculation.)

                      • 8. Re: IfCell IsEmpty GetData from AnotherCell
                        trunkdog

                        Hi Phil and thank you. I suppose I do a poor job of describing my intent. Ultimately I am looking for the ability to work with an existing (imported) database. Should one of the fields require populating with data from another field I would like syntax for that. At first I tried changing the workstat field to type = Calculation; with a "NOT IsEmpty" statement, thinking this would retain the integreity of the data currently in that field, yet populating witjh the remainder of the calculation. Instead I got a result of "0". I assume answering the "NOT IsEmpty" request. What I opted to do was:

                        • Create a "seed" field to supply the need for a generic "answer" to the case where all three existing fields in the query are empty
                        • Set this field initially to Type = Calcualtion "N/A" to pre-populate all records
                        • Change the Type for this "seed" filed to Text; allowing the following Calculation to populate the workstat field records.
                        • Create a field <state> to, in effect, replace the workstat field


                        This "state" field has the Calculation:
                        Let ( [ w = Trim ( workstat ) ; h = Trim ( homestat ) ; l = Trim ( licnstat ) ] ;
                               Case (
                                       IsEmpty ( w & h & l) ; holdstat ;
                                       IsEmpty ( w & h) ; l ;
                                       IsEmpty ( w ) ; h ;
                                       w
                                       ) //case
                             )//Let

                        I now search and report using the "state" field.

                        Again, thank you for your invaluable and accurate assistance. Cool
                        Barry

                        • 9. Re: IfCell IsEmpty GetData from AnotherCell
                          philmodjunk

                          Should one of the fields require populating with data from another field I would like syntax for that.

                          The expression we've been working with is an example of how to do exactly that.

                          Do you know how to set up an auto-enter calculation?

                          Take the existing field that needs to copy a value from another field if it is empty. Find it in manage | Database | fields. Double click it there to bring up field options. Click auto enter. Select calculation and enter an expression to copy the value from the other field. This can be as simple as entering the name of that other field. Since you only want this to take place if the field is empty, do not clear the "do not replace existing value..." option. This will work for each new record and each newly imported record if you enable auto-enter options during import.

                          To update existing records, you can use Replace Field Contents in a calculation to copy a value from one field into the field for all records where it is empty. Since replace field contents works on the found set, do a find for all records where this field is blank then do your Replace Field contents. Since this can change a lot of records and you can't undo it, save a back up copy of your file before you try to do this.

                          • 10. Re: IfCell IsEmpty GetData from AnotherCell
                            trunkdog

                            Bingo!Replace Field Contents on a found set, thanks! I'll give it a go. I appreciate the auto-enter calc scenario as well.