On an earler question, mlmcc helped me to split some information in a report. I'm trying to use the same formula in a similar report and come across some errors.
I have a list of part numbers and descriptions. They describe a thread diameter and length. My formula is as follows:
Local StringVar strForValue;
Local StringVar Array strLengthList;
Local StringVar Array strFractionList;
strForValue := Trim(Split(Split({p21_view
_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);
strForValue := Replace(strForValue,'"','');
strForValue := Replace(strForValue,'-',' ');
strLengthList := Split(strForValue,' ');
If UBound(strLengthList) = 1 then
Val(strLengthList[1])
Else
(
strFractionList := Split(strLengthList[2],'/');
Val(strLengthList[1]) + Val(strFractionList[1]) / Val(strFractionList[2]);
)
I will get an error in the formula in a couple of different places. One would highlight the very last phrase (strFractionList [2]); and another would highlight up in the beginning of the formula where it says {p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);
First off: Could someone explain to me what this formula is doing? Maybe then I could figure out which "Descriptions" in my data it does not like.
Secondly: Does it matter if the description has a lower case "x" eventhough the formula calls for an uppercase "X"?
If I understood the formula I may be able to change the descripton on some of these parts.
What I end up doing is running the report and going page by page until it causes an alert then I look at the page it stopped on and try to find funky looking descriptions and either filter them out of edit the description.
For instance, I know that it bombs out when it reads this description: 1 1/2-8(UNJ)X63.5DE STUD 4340 COLD ROLL but if I put a space before and after the "X" it will read fine. So I understand the it requires this but I'm not sure of anything else that I have to have.
I have figured out that the formula doesn't like when my description is in feet and inches. For example 5/8-11 X 20'-0" DBL END STUD 4 &10 TOE would bomb. So is there a way to include this in my formula?
Naturally, it will also bomb if there is not a diameter and length like in "5" SUPPORT STUD" so I am filtering them out individually. Again, if there is a way to eliminate this in the formula that would be great because now I have to go page my page to try and find one of these. Currently my report is only for fiscal year 2013 which start 10/1/2012 so I don't have a lot of data. But I want to remove this filter and have several years worth of data so going page by page won't be an option.
DE-History-for-Mike.rpt
Split({p21_view_inv_mast.i
This splits the string into parts using the ' X ' as the split term. Notice the spaces around the X. That is why you have a problem if the spaces aren't there. The -1 says to return all substrings. The 1 says use a comparison that is NOT case sensitive so an x or X will match.
It then returns the second string found.
The returned string is then split on the spaces and the first part returned which is then trimmed of any spaces it might have at the start or end.
Replace(strForValue,'"',''
Replaces the inch " notation with nothing
Replace(strForValue,'-',' ');
Replaces the DASH - with nothing
You can replace ' with
Replace(strForValue,"'",' ');
Can you provide a good sample of the data in a spreadsheet. Just need that field.
Also would be helpful to provide the value you want to return from the formula
ALso explain what (UNJ) means and does it affect the value?
mlmcc