1 of 1 people found this helpful
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
If I script this out, can I be sure that all the data will go to the appropriate invoice?
"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. :-)
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.
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.
I agree, you should definitely search for the records that don't have 3 lines.
All data in that field is indexed text and set up like this:
1234 This St.
Do you see any problem with this format that I may have?
1 of 1 people found this helpful
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.
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?
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
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?
This is for a delivery service and represents where the stops were.
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.
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.