3 Replies Latest reply on Mar 29, 2011 3:17 AM by LaRetta_1

    How to Extract Data and Add to another field after a character?

    bookalaka_1

      Title

      How to Extract Data and Add to another field after a character?

      Post

      I receive an inventory report that I need to manipulate not sure how to break the information out.  imported into filemaker from excel my first field  data reads; LOV...AA, 2ND FIELD; N1001009 ALBACORE. The end result shoub be Field 1_ITEMCODE: AAN1001009 Field 2; ItemName:ALBACORE  Field 3 STATUS; LOV

      Currently my script is

      go to field[table_A::itemcode]

      perfom find/replace["lOV...","" replace&find]

      setfield [table_A::Status"LOV"]

      Where I am stuck is on the 2nd Field. how do I get the 8 characters over to the first field and paste after the first two characters ending up with "AAN1001009"

      If I use Right ( TABLE_A::description ; 8 ) this extracts the characters but  doesn't give me what I need to do which is to paste to the first field.

      Thanks for guidance.

        • 1. Re: How to Extract Data and Add to another field after a character?
          LaRetta_1

          I would leave your raw data fields alone and simply pull each piece needed and set new fields.  To test that the data will properly be translated, create the following three new text calculations, repointing where I say Data1 and Data2 to your original two data fields:

          cItemCode:  Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 )

          cItemName:  RightWords ( data2 ; 1 )

          cStatus:  LeftWords ( data1 ; 1 )

          Once you are sure, by checking through your records, that these calculations produce the correct results (be sure the result type of all three fields is TEXT), then you can create your three standard text fields ItemCode, ItemName and Status and script setting the fields with something like this:

          Show All Records
          Replace Field Contents [ ItemCode ; by calculation ; Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 ) ]
          Replace Field Contents [ ItemName ; by calculation ; RightWords ( data2 ; 1 ) ]
          Replace Field Contents [ Status ; by calculation ; LeftWords ( data1 ; 1 ) ]

          If this is multi-user file then run it when no Users are in the system to protect from record locking.

          • 2. Re: How to Extract Data and Add to another field after a character?
            bookalaka_1

            Well it does work however thier is a  problem that i really didnt define clearly. Not every entry has the "LOV" tag this is the exception not the rule.  So a correct entry which I will import will be Itemcode:LSN1001009, ItemName: White Wine , Status: (I created to know once the code was parsed out which items would be"LOV"). So now with the calc fields defined the other entries  become incorrect. How can I define to only the entries that have "LOV"?

            Thanks for the edification! This forum is quite amazing.

            • 3. Re: How to Extract Data and Add to another field after a character?
              LaRetta_1

              Hmmm, I do not know if this is a one-time thing or an ongoing process but you now have a Status field will LOV or LSN etc.  So you can perform a find for LOV in field Status and run your script through those.

              Otherwise, you can perform a find on Text1 for:  ==LOV*

              This will isolate the records you wish to split.

              Optionally, for viewing the calculations only on those entries beginning with LOV to ease the stress on your eyes, wrap blue portion around your first two calculations  (example on your first calc):

              Case ( LeftWords ( data1 ; 1 ) = "LOV" ;
              Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 )
              )