1 2 Previous Next 19 Replies Latest reply on May 24, 2013 5:42 AM by petermontague1

    Import a list of keywords into a set of fields that have a maximum of 50 characters

    petermontague1

      Title

      Import a list of keywords into a set of fields that have a maximum of 50 characters

      Post

           I would like to import a list of keywords into a set of four fields called search terms 1, search terms 2 etc. I can have a maximum of 50 characters in each field. So when the maximum is reached, or passed, I would like the import to the field to stop at the word which does not break the maximum allowance and continue the import into the next search term field. I don't know where to start. Can you advise me please?

        • 1. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
          philmodjunk

               Do they have to be 4 fields or might they be 4 (or more) related records? Might make for more flexible database design to add a related table for this.

               What is the format of the data that you are importing?

               Knowing that detail, you can probably import the list into a regular text field with four fields that auto-enter portions of the same field to get your four fields or a script can move the data into related records after the import.

          • 2. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
            petermontague1

                 The maximum is four fields. There is no problem with them being related records. I could set up a separate table for this. And then make a new record for each of the fields. I presume this would be a one to many relationship. The data is text. I already have the data in a field in my inventory. I'd like to be able to split this up into the four fields as necessary.

                 I'd like to be able to auto-enter portions of my keywords field.

            • 3. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
              philmodjunk

                   Yes, but what is the format of your data? What charater(s) separate each keyword?

                   I'm not sure if there will be much advantage to having a related table for this is you are stuck with a rigid maximum of 4 such fields. Then again, if the external system that imposes this limit ever changed in the future, a related table would make it easier to adapt to such a method.

                   But a related table will require using a script to move your data into records of the related table in most cases.

              • 4. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                PeterMontague

                     Each keyword is separated by a comma. I think I could calculate the first search terms field to be something like this:

                     

                          If ( Length ( Inventory::search_terms1 ) ≥ 50 ; LeftWords ( Inventory::search_terms1 ; ....... ; ..... )

                     I'm lost after this.

                • 5. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                  PeterMontague

                       By the way I logged in yesterday using a new id as I couldn't remember by username and password.

                  • 6. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                    philmodjunk

                         Try:

                         Let ( L = Trim ( Left ( inventory::Search_terms1 ; 51 ) )  ;
                                 Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) )
                          

                    • 7. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                      petermontague1

                           Thanks Phil. That works. I adapted it like this:

                      Let ( L = Trim ( Left ( Inventory::keywords ; 51 ) )  ;
                                    Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) )

                           I'm copying the words from the keywords field into the search_terms1, search_terms2, search_terms3 and search_terms4 fields.

                           Sometimes the fields already have some text in them. I don't want to overwrite any text that is already there. I could make a calculation like this:

                           =search_terms1 & Let (L = Trim(Left(Inventory::keywords ; 51)) ; Left ( L ; Position ( L ; "," ; Length (L) ; -1) -1) )

                            

                           Or I could replace the field contents of keywords with the contents of the search terms fields if they are not empty and then start from there.

                      • 8. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                        petermontague1

                             In many of my search terms fields I have information already entered. How can I get this calculation to take into account the text that is already there and to still not exceed 50 characters?

                        • 9. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                          philmodjunk

                               Does that mean you have two fields, one with the new data and one with existing data?

                               Otherwise, your import will overwrite the existing data if you import directly into that field.

                               With two fields, involved...

                               Let ( L = Trim ( Left ( Inventory::ExistingKeyWords & "," & Inventory::ImportedKeyWords ; 51 ) )  ;
                                        Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) )

                          • 10. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                            petermontague1

                                 Thanks Phil. I've adapted it a little bit so that if the search terms field is empty there is no comma at the beginning of the text.

                                  

                                 Let ( L = Trim ( Left ( Inventory::search_terms1 & If ( IsEmpty ( Inventory::search_terms1 ) ; "" ; "," ) & Inventory::keywords ; 51 ) )  ;
                                          Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) )
                                  
                                 I was wondering: how would I adapt this to use the word after the ending point in Inventory::search_terms1 as the starting point in search_terms2?
                                 Also sometimes the keywords field has no commas in it. How should I alter the calculation to allow for no comma?
                            • 11. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                              philmodjunk
                                   
                                        I was wondering: how would I adapt this to use the word after the ending point in Inventory::search_terms1 as the starting point in search_terms2?
                                   Can you give an example of what you mean by that?
                                   
                                        Also sometimes the keywords field has no commas in it. How should I alter the calculation to allow for no comma?
                                   If there are no commas in some cases (nothing like inconsistent data to make things complicated...), how are the terms delimitted?
                                    
                                   Whatever character separates the terms, whether spaces, returns the pipe character, you can use pattern count to detect that there are no commas and then use the subsitute function to add them in place of or in addition to the delimitting character...
                              • 12. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                                petermontague1

                                      

                                Here is the keywords field contents:

                                Daniel Evan Weiss, Unnatural Selection, Black Swan, 0552993603, 1342, Modern fiction, Fiction, General, Fiction General, General & Literary Fiction.

                                      

                                Here is the search terms after applying your calculation:

                                      

                                Daniel Evan Weiss, Unnatural Selection, Black Swan

                                I would like to continue, in search_terms2 with "0552993603" for another 50 characters, finishing at the next word.

                                     

                                how are the terms delimited?

                                There is no delimiter when there is just one term: E.g. Daniel Evan Weiss (the author's name).

                                • 13. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                                  philmodjunk

                                       To handle a list of a single term, you could modify your calc as:

                                       Let ( L = Trim ( Left ( Inventory::search_terms1 & If ( IsEmpty ( Inventory::search_terms1 ) ; "" ; "," ) & Inventory::keywords ; 51 ) )  ;
                                                If ( Position ( L ; "," ; Length ( L ) ; -1 ) - 1) ; Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) ; L )
                                        
                                       Am I correct that you want to load a second field with the next 50 characters worth of keywords?
                                        
                                       Then this expression should serve to return all text NOT returned by the first calculation:
                                        
                                       Right ( CombinedKeywordText ; Length ( CombinedKeywordText ) - Length ( FirstCalcProducedKeywordText ) )
                                        
                                       This could then be fed into the very same calculation used for the first field.
                                  • 14. Re: Import a list of keywords into a set of fields that have a maximum of 50 characters
                                    petermontague1

                                          

                                         
                                    Am I correct that you want to load a second field with the next 50 characters worth of keywords?
                                    Yes.
                                         
                                    This could then be fed into the very same calculation used for the first field.
                                         Is this what you mean?
                                         Let ( L = Trim ( Left ( Inventory::search_terms2 & If ( IsEmpty ( Inventory::search_terms2 ) ; "" ; "," )
                                         & Right ( Inventory::keywords ; Length ( Inventory::keywords ) - Length ( Inventory::search_terms1 ) ) ) )  ;
                                         If ( Position ( L ; "," ; Length ( L ) ; -1 ) - 1 ; Left ( L ; Position ( L ; "," ; Length ( L ) ; -1 ) - 1) ; L ))
                                         I'm getting an error message when I enter the Right calculation in there. It says there are too few parameters.
                                         The Right calculation on its own works in choosing the second 50 characters from the keywords field. But sometimes I still don't need to take the second 50 characters from keywords because there had already been words in search_terms1 and therefor less than 50 words had been taken from the keywords field. I could make this work by making a composite new field which concatanates the keywords and search_terms (where there are search terms) and then delete everything in the keywords fields and then start the calculation with just blank search_terms fields. I guess that would make things easier.
                                    1 2 Previous Next