1 2 3 Previous Next 34 Replies Latest reply on Jul 15, 2011 12:06 PM by VesaPakarinen

    Select text from right

    VesaPakarinen

      Title

      Select text from right

      Post

      Hello. I am newbie with FMP. What is the easiest way to select text from field. There is text as "rp456-J or rb234-U+" and I need to select text until "-" from since right. Thank you, vepakari

        • 1. Re: Select text from right
          ryan

          to clarify: you want all the text to the right of the hyphen (-)?

          based on your examples rp456-J or rb234-U+, you want "j" or "U+" returned?

          • 2. Re: Select text from right
            VesaPakarinen
            Sorry. There is "codes" as rp456-j, rb234-u and many more. I want characters that are right side of "-" will be returned to different field. Vesa
            • 3. Re: Select text from right
              philmodjunk

              Let ( [ L = Length ( YourTextField ) ; P = Position ( YourTextField ; "-" ; 1 ; 1 ) ] ; Right ( YourTextField ; L - P ) )

              • 4. Re: Select text from right
                VesaPakarinen
                Thank you PhilModJunk. It works well. I is easy when you know. An other question: What is easiest way to delete record, when there is a empty field. I make case function what returns string, if string is empty hole record must be deleted. Loop but...? Thanks Vepakari
                • 5. Re: Select text from right
                  philmodjunk

                  No need for a loop.

                  Perform a find to find all such records where the field is empty. This can be done by putting an = by itself in this field.

                  Then do a delete found set. In a script, this step is called Delete All Records, but it really only deletes the found set.

                  • 6. Re: Select text from right
                    VesaPakarinen
                    Thanks again. It works well. There is again problem. I imported csv file in my table. In field (number) there is numeral like 123.34 and it should be 123,34. How I can convert the number comma separated. If I calculate like 123.34 *10, I get 123340 not 1233,4. Best regards Vepakari
                    • 7. Re: Select text from right
                      philmodjunk

                      Simplest would to be to get whatever source generated the csv file to use european number formats instead of US formats.

                      The other option would be to use the following calculation with Replace Field Contents to replace "." with ",":

                      Importfield / 100

                      • 8. Re: Select text from right
                        VesaPakarinen

                        Hello,

                        Can You expain me a little pit more?  I have field "cPrice" that is american numbers like 340.34 and I must calculate 340.34 * 1,44. cPrice must be (result) 490.0 or 490 after calculation, cause field must be exported like "american" csv where using "." insted ","

                        Vepakari

                        Ps. I have miserable to create script to import csv file. There is lot of record in csv. -file, but to import next rules must be fulfilled: one field like ID must be in current table, else this csv-record not imported....

                        • 9. Re: Select text from right
                          philmodjunk

                          The presence of "." or "," in your number fields should be a format that does not affect their value. Thus, what we are discussing should not affect the value returned by cPrice as long as the number fields it uses store the correct values.

                          You may need to set up a calculation like this to replace "." with "," to get the correct value imported into your field:

                          Substitute ( YourTable::YourField ; ["," ; "" ] ; ["." , ","] )

                          You might try this as an auto-enter calculation on the field and then enable auto-enter options during import. (Not sure if that will work or not, so try it and see.)

                          If that doesn't work, import the value into a global text field and set the auto-enter expression to take the value of the global field and use substitute to change the decimal symbol from . to ,. (You still need to enable auto-enter options during import.) (Learned this trick from LaRetta.)

                          If all else fails, you may need to import the values into text fields and then use this calculation with a script to reformat the text, then convert them into numbers while copying them into the actual number fields you will use in your database.

                          • 10. Re: Select text from right
                            VesaPakarinen

                            Thanks a lot PhilModJunk. Now calculation works. You have give me lot of help. Dare I ask help about this script....I have miserable to create script to import csv file. There is lot of record in csv. -file, but to import next rule must be fulfilled: one field like ID must be in current table, else this csv-record not imported....

                            Regards, Vepakari

                            • 11. Re: Select text from right
                              philmodjunk

                              Is this ID field a filemaker field with an auto-entered serial number or is this an ID number that is supposed to be imported with the data?

                              If it's a fileMaker serial number field, enable auto-enter options during import and it will automatically assign ID numbers to each newly imported record and this will keep the validation rule from excluding your imported records.

                              • 12. Re: Select text from right
                                VesaPakarinen

                                No. the field is like RB427A-P or RFDEA-Q ,etc. The field must be imported too.

                                • 13. Re: Select text from right
                                  philmodjunk

                                  And you are importing this into a text field, but get the error message? That indicates that your source file lacks a needed ID number. Either you'll need to get a copy of the csv file where every row contains this value or you'll need to remove this field validation setting on the FileMaker field.

                                  • 14. Re: Select text from right
                                    VesaPakarinen

                                    Good Morning PhilModJunk. I am wondering what kind of auto-enter script must I write. Loop? 

                                    Regards, vepakari

                                    1 2 3 Previous Next