VB Formula in space object based PSD – Help!

[Anonymous]

 I would be very thankfull if anyone familiar with VB formulas in PSD could take a look and give some directions?

I created list definition for materials and finishes to use it for space finish schedule.

The list is made with abbreviations following the full name of the materials like

“VCT – 1 ~ VINYL COMPOSITION TILE”.

I created style based PSD for spaces and object based PSD for spaces including some VB formulas so the name will be trimmed to read only abbreviation like “VCT – 1”.

The idea is to use full names of materials from the list and set all finishes and materials in the space style and occasionally overwrite some of the entries in object based PSD, and report only abbreviations in one single schedule per wish as follows:

Type of Schedule “1”

Report Space style based finishes stacked over space style based materials.

Type of Schedule “2”

If there are no overwritten entries report space style based finishes stacked over space style based materials as for Type of Schedule “1”.

It there are overwritten entries for finishes or materials or any of them report overwritten entries of the finishes stacked over overwritten entries of materials, otherwise report style based entries.

Type of Schedule “3”

If there are no overwritten entries report only style based finishes.

If there are overwritten entries report object based finishes.

When I use some of common abbreviations as overwritten entries like

“EXISTING”, “VERIFY IN FIELD” or “NOT REQUIRED” the formula does not return appropriate value as I need.

I posted small version of the file in ACA 2008 so you can see my intention.

Can this be done at all?

[David_W_Koch]

I took a quick look at your file and I am afraid I am not following exactly what you are trying to do, making it hard to follow the logic of your formula property and work out what is going wrong.  If I misunderstood something, post back with more details.

It appears that you are taking a value that the user selects from a List Definition that includes extra explanatory data (to aid the user in selecting the correct item??) and then extracting part of that for use in the schedule.  The WordSpllitNumber property's function appears to be related to breaking up the user-selected string into two pieces.  I would think that leaving that as a user-set value could be problematic, but perhaps it works for your situaltion and workflow, especially if it does not need to be changed from the default value of 3 very often.

Is the formula splitting the list values properly?  In your example, the finish and base material values are the same, so I am not certain if that part is really working for you.  If there are problems there, I would suggest breaking your single formula property into a series of separate properties, each with just one task, to make it easier to see results and debug any errors.  I would have separate formula properties to generate finsh, the base material and the abbreviation of your non-material-specific overrides, and then have a separate formula property that combines the desired parts based on the schedule type.

The problem with some of your non-finish-specific overrides is that they have spaces in them, and the first part of your formula is operating on them.  With WordSplitNumber set to 3, "-VERIFY IN FIELD-" gets cut down to an empty string and the RESULT becomes "ENTER".  Even when it is set to 1 or 0, it gets cut down to "IN FIELD-" and is therefore not found in the second Select Case statement, leaving "-VERIFY" as the finish value in the returned string.

You need to change the logic by which you determine whether the MaterialString needs to be cut down in size.  Can you modify your List Definition so that all of the non-material-specific overrides include a "special" character that would never be used in a material-specific finish description?  You could then test for that character and process the string accordingly.  On looking at your List Definition, it appears that you are using a "~" character in all of your material-specific options, except for the Brick material, and I am going to assume that is a typographical error.  I would suggest testing for that character.  Since this is the character that divides the part you want from the part you do not want, you might also want to use that to cut off the balance of the string, rather than having to set a number of spaces that are to remain and looping through a number of iterations to get there.

In the attached file, I changed your formula to initially test for the "~" character to determine if the MaterialString value needed to be truncated, and it appears to work in the limited testing I did.  You set TrimString to the value you wanted, but then did not make use of that variable in the SelectCase statements.  I made use of the TrimString variable.  (I also edited the List Definition to put a "~" in the Brick material value.)

I created a second formula property for both Finish and Material (same name, with a "2" on the end), that does the truncation based on the "~" character, without the need for the user to provide a proper value in the WordSplitNumber property.  (If you adopt the alterate formulae, you can delete the WordSplitNumber property.)  One difference between the two formulas is that in the original formula, you left a space character at the end of the TrimString, so when it gets concatenated with the material string, there are two spaces entered.  I used the Trim function in the second formula to eliminate the trailing space character.  If you want two spaces there, then delete the Trim function.  If you do not want two spaces, then add the Trim function to the original formula (if you continue to use that).

I am assuming that in your final lists and actual usage, that the Finish and Material choices would not typically be identical.

[Anonymous · Accepted solution]

David, I am very thankful for this great explanation. Your assumptions were correct. Your suggestion for using character “~” as a base for truncation as well as the changed formula will work in our work flow so we will have material and finish definition in this format Ex:(“VCT~VINYL COMPOSITION TILE”) for the situation where we have the same material/finish through the building and if we have some variations in color, manufacturer it could be quickly manually overwritten adding the suffix in front of character”~” Ex: (VCT-1~VINYL COMPOSITION TILE”). Also, ones we make it work we intend to populate List Definition for materials and make a separate List Definition for finishes. Once again, thank you very much for help.