1 2 Previous Next 15 Replies Latest reply on Nov 13, 2012 12:44 PM by dgp_express

    Need some help with invoice line items

    dgp_express

      I have a field in my invoice line items that is a three line block Company name and address.

      I know now that I should have made it three different fields. (Still learning from my mistakes )

       

      I have been trying to figure out how to auto populate this field if the data already exists in a type ahead style.

       

      I can do it if I keep clicking back into the field for each line of data but that is a pain.

       

      Is there a way to set the tab to go to the next line rather than jump to another field on the layout?

      It is typically the last field to put in data in the set tab order.

       

      If not, is there a way to set this up without risk of loosing the 5000 plus records in this field?

       

      Any help with this would be very much appreciated.

        • 1. Re: Need some help with invoice line items
          thomas_staehli

          Hi,

           

          It's not too late to repair the mistake. If you can certify that Line 1 is Company, Line 2 is Name and Line 3 is address, I would start by separate the data into 3 other fields.

           

          There's a quick way to do that, without risking of loosing any data. Just create 3 fields. Go on a layout that would show the list of all 5000 records. Make sure you display your 3 new fields on it. Click in the first one (company for example), then use the "Replace field content" function, and select "replace using calculated result"

           

          As company is the first line of your original field, you can use the following calculation:

           

          Substitute ( MiddleValues ( AddressField ; 1 ; 1 ) ; ¶ ; "" )

           

          This will replace the new company field ( that is empty as you just created it ) with the first line of your original address field. As the MiddleValues leaves a ¶ at the end of the string, I added a substitute  function to remove it.

           

          To get the seconde line:

           

          Substitute ( MiddleValues ( AddressField ; 2 ; 1 ) ; ¶ ; "" )

           

          and the third line

           

          Substitute ( MiddleValues ( AddressField ; 3 ; 1 ) ; ¶ ; "" )

           

          Once you have the fields separate, you can just replace the original field by the 3 new fields and setup the correct tab order between them

           

          I hope this helps

           

          Thomas

          1 of 1 people found this helpful
          • 2. Re: Need some help with invoice line items
            dgp_express

            If I script this out, can I be sure that all the data will go to the appropriate invoice?

            • 3. Re: Need some help with invoice line items

              "I have a field in my invoice line items that is a three line block Company name and address.

              I know now that I should have made it three different fields. (Still learning from my mistakes  )."

               

              Correcting the addresses is the way to go as Thomas indicates.  You can create three calculations and review the results before changing the data and back up first.  However, if you have one field with three LINES of data you can split them as:

               

              GetValue ( yourField ; 1 )

               

              ... replace 1 with 2 to get the second etc.  Now, since you have TEXT calculations and have confirmed that the data is correct, change your DATA from your field definitions directly.  You have indexed text calc for Company which shows correct results through all records.  Back up.  Then change that calc to type text and exit field definitions.  Since the data is indexed it will be planted as data into all the records - no need to show all records or go to the layout.

               

              One caution ... if you have addresses with only one or two lines you will need to handle them a bit differently because FM will not know whether it is Name missing or address.  I would suggest creating a calculation (result is number) with:

               

              ValueCount ( yourField )

               

              Then search for addresses in batches, correcting those with three values, then search for those with 2.  :-)

              • 4. Re: Need some help with invoice line items

                I sure wish I could correct my entries from iPad like most other sites...

                 

                I wanted to clarify that if you have two-line addresses then you can't change the data from field definitions since that ALWAYS applies to all records in the table or else include tests in your calculation on what to do if only two lines.

                • 5. Re: Need some help with invoice line items
                  thomas_staehli

                  Yes. The replace function based on a calculation basically does a loop on all the records in the found set and apply the calculation on each record.

                  • 6. Re: Need some help with invoice line items
                    thomas_staehli

                    I agree, you should definitely search for the records that don't have 3 lines.

                    • 7. Re: Need some help with invoice line items
                      dgp_express

                      All data in that field is indexed text and set up like this:

                       

                      This Company

                      1234 This St.

                      City, State

                       

                      Do you see any problem with this format that I may have?

                      • 8. Re: Need some help with invoice line items
                        thomas_staehli

                        As long as all the records have those 3 lines, there's no problem at all.

                         

                        In your example:

                        GetValue ( FieldName ; 1 ) will return "This Company"

                        GetValue ( FieldName ; 2 ) will return "1234 This St."

                        GetValue ( FieldName ; 3 ) will return "City, State"

                         

                        If you always have a comma and a space after city, you could even separate City and states into 2 different fields.

                        1 of 1 people found this helpful
                        • 9. Re: Need some help with invoice line items
                          dgp_express

                          Thank you so much for the help.

                           

                          Will this loop through all the records or will I have to script in get next record?

                           

                          And will this work for several line items on one invoice?

                          • 10. Re: Need some help with invoice line items
                            thomas_staehli

                            Just to be sure, you have an invoice table and a line item table right? Is the company something that is specific to an invoice or is each line item linked to a specific company?

                             

                            Now, concerning the replace field content function, check the attached video to understand how it works

                             

                            • 11. Re: Need some help with invoice line items
                              Lemmtech

                              Why do your line items have company information in them? Line items are usually for products being sold, what is the purpose of repeating a companies address over and over again in your line items?

                              • 12. Re: Need some help with invoice line items
                                dgp_express

                                This is for a delivery service and represents where the stops were.

                                • 13. Re: Need some help with invoice line items
                                  Lemmtech

                                  Then all you have to do is place the customer ID in each line item (not the full address) and then run a deliver report that summarizes all the items being delivered by customer. Would be much more effeficent way to go about it.

                                  • 14. Re: Need some help with invoice line items
                                    dgp_express

                                    Worked perfectly. Thank you so much for your help and patience.

                                     

                                    To think of the late nights I have agonized over this solved in less than 3 minutes.

                                    1 2 Previous Next