10 Replies Latest reply on Sep 5, 2009 9:39 PM by JeffJ

    Producing non-printing/delimiting/repeating field characters in a formula with text result

    JeffJ

      Title

      Producing non-printing/delimiting/repeating field characters in a formula with text result

      Post

      I am attempting to bring a set of records from one database into a group of repeating fields in another in FM 5.5. Please don't ask why. So far I have tried several methods with little success but my latest is promising with two basic problems. The first is the topic of this post. The following is a representation of a formula I have created to facilitate this:

       

      SetField("gGlobalTextField","TextField1 & Char(29) & TextField2")

       

      The Char(29) figure would be what I understand is the repeating field delimiting character if there was a Char() function in FM 5.5. Does anyone have any thoughts on how to produce a text line as outlined above? Thanks

        • 1. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
          comment_1
            

          I haven't tried this, but I think you could paste the character into a global field and then refer to it in a calculation formula, e.g.

           

          TextField1 & gChar29 & TextField2

           

           

          Another option is to export as XML and use a XSLT stylesheet to insert the delimiter.

          Sorry, I didn't realize the source file was also in v.5.5.

          • 2. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
            mrvodka
              

            I dont think this will work. Even if you concatentate the Char (29) character into the calc field, when importing into the repeating field, I believe it will import it into only the first rep with perhaps the infamous "Square" character representation of it.

             

            The only other way I would think would be to use a loop to set each rep after importing into a temp global or field for each record.

            • 3. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
              philmodjunk
                

              Since this is in 5.5, it's further complicated by the fact that you can't refer to a repetition via a calculated expression. You end up with a God awful nested series of IF thens, where each branch of the if statement contains a different set field with an explicit repetition reference.

               

              Really nasty code, but I don't think you have any practical alternative without upgrading to a newer version of FMP.

              • 4. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                mrvodka
                  

                PhilModJunk wrote:

                Since this is in 5.5, it's further complicated by the fact that you can't refer to a repetition via a calculated expression. You end up with a God awful nested series of IF thens, where each branch of the if statement contains a different set field with an explicit repetition reference.

                 

                Really nasty code, but I don't think you have any practical alternative without upgrading to a newer version of FMP.


                I dont think that would be the way to go anyway.

                 

                I much rather Go to the field on the layout and then use Go to Next Field to cycle through the repetions. Then you can use Set Field or Insert Calculated Result ( without specifying a target) to set the value.


                • 5. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                  davidanders
                    

                  To import into a repeating field, save your data into a delimited text file, and use Word to replace the delimiter between the repeating values with Ascii character 29 (^29 in Word). Save out as a delimited text file again, and import into FM. For example:

                  Rcp01,item1[^29]item2[^29]item3[^29]item4
                  would import 4 items into a repeating field in the RCP01 record.

                  (When you do the search and replace in Word, ^29 will appear as a small box)

                   



                  • 6. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                    JeffJ
                      

                    I want to sincerely thank all who answered my post. The problem, however, remains. I would like to offer a more complete description of what I have done in hopes that anyone might be able to add the two missing pieces.

                     

                    I am working with FM 5.5 and attempting to bring the data from a group of line item records, related to an invoice by invoice number in a separate file, into an invoice in an other database with a set of repeating fields as the line items. I have written several interconnected scripts that, among other things, move through the main invoice file, show/find related records in the line item file, and then move through those related line items filing global variables to be exported. This is done with a loop that starts at the first related line item as below:

                     

                    Go to Record/Request/Page [ First ]
                    Set Field [ gExpQty, "" ]
                    Set Field [ gExpProdId, "" ]
                    Set Field [ gExpDesc, "" ]
                    Set Field [ gExpProdCat, "" ]
                    Set Field [ gExpItemPce, "" ]

                    Loop
                       Set Field [ gExpQty, gExpQty & NumToText(Quantity) & gChar29 ]
                       Set Field [ gExpProdId, gExpProdId & ProductID & gChar29 ]
                       Set Field [ gExpDesc, gExpDesc & Description & gChar29 ]
                       Set Field [ gExpProdCat, gExpProdCat & Proper(ProdCat) & gChar29 ]
                       Set Field [ gExpItemPce, gExpItemPce & NumToText(Item Price) & gChar29 ]
                       Exit Loop If [ Status( CurrentRecordNumber) = Status( CurrentFoundCount) ]
                       Go to Record/Request/Page [ Next ]
                    End Loop

                    Export Records [ Filename: "merge.csv"; Export Order: gExpQty(Text), gExpProdId(Text), gExpDesc(Text), gExpProdCat(Text), gExpItemPce(Text) ]
                    [ Restore export order, No dialog ]

                    The problem is the 29, or repeating field, character. The only way I have found to produce it is to copy it from a previously exported .csv file of repeating fields. When I paste it directly into the Set Field functions as "invisible" text it exports as common spaces. It does the same if I paste it into a global text field and use that in the Set Field function as above. That is problem one of two.

                     

                    The second is the export, and following that, the import function. As you can see above it is set to export to "merge.csv" with a previously saved order and no dialog. The order is fine but the dialog still appears with no filename in the name box and it has to be chosen manually. The same is true of the import command later in the procedure that is not pictured here.

                     

                    Any ideas folks? Thanks






                    • 7. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                      comment_1
                        

                      You cannot paste a non-printing character into a calculation formula - you must use a global text field. I don't have version 5.5, but I have tested this with version 6 and it works perfectly. My guess would be that you haven't been succesful in grabbing the right character.

                       

                      Same with the export/import script steps: when the 'Specify File…' and 'Perform without dialog' options are checked, the script runs by itself without asking for directions.

                       

                       

                      • 8. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                        JeffJ
                           Thank you for your reply. I have to beleive it is FM 5.5. I have thoroughly tested the character itself and, as stated, tried both pasting into a formula and pasting into a global text field to be used in a formula. As you can see the export is set to run without dialog but it still asks for a file name. For these issues, and many others, I beleive it is time to ask the client to upgrade. Since cost is always an issue can you suggest, short of the latest version, what version might be effective on older Pentiums and supply some other features such as memory variables for programming, email of layouts/printouts for the client, PC and Mac data compatiblity, and web access features? Do you know of any resources that might present a comparison of the different versions? Thanks
                        • 10. Re: Producing non-printing/delimiting/repeating field characters in a formula with text result
                          JeffJ
                             Thank you soooo much comment. Even though I had copied the repeating delimiter from an exported file it did not work properly like the one in the global text field of the database you directed me to. In fact, your answer led me to a complete re-write of my process. Using the new delimiter I build the global export fields in the line item database as I did before, then copy and paste the contents of those fields, one by one, into matching global fields in the related invoice record, then export the entire record to a comma delimited file and import it into the new invoice file that contains repeating fields. I just have to coordinate the export and import orders. Works great! Thanks again