How can we help you today?
Getting more than one virtual field to work with API code.
2 CommentsSorted by Oldest First
Hi Paul - I have a couple of questions/comments for you:
1. Why are trying to create a virtual column in code? Why not just add the virtual column in the Profile UI then all you have to do is execute the line you have in your function in something like BeforeDictionaries or AfterDictionaries:
Import.Fields.GetByExcelName("ExcelField").Value = Import.Fields.GetByExcelName("K").Value + " " + Import.Fields.GetByExcelName("L").Value
2. In AfterDictionaries you could do something like the following:
Dim zipCode as string = Import.Fields.GetByExcelName("ZipCodeExcelField").Value
if (zipCode.Length > 0) and (zipCode.Length Import.Fields.GetByExcelName("ZipCodeExcelField").Value = zipCode.PadLeft(5,"0")
end if
PadLeft will append leading 0s to make the total length of the string 5 characters for the zipcode (in my example). Obviously zip+4 processing the logic will be different to account for the +4.
It seems you are trying to do everything in code. I think if you consider that you don't need to create all of these functions with computed columns when you can overwrite or write to all columns/fields in a profile at various events. Just as I did in the Zip Code example, above I take what the profile has for that row and IF I need to change that value I write it back to the same column with the new value. And based on your first question I think it is easier if you just create a Virtual Field in the Profile UI and just write directly to that field with the concatenated values.
I hope this helps.
1. Why are trying to create a virtual column in code? Why not just add the virtual column in the Profile UI then all you have to do is execute the line you have in your function in something like BeforeDictionaries or AfterDictionaries:
Import.Fields.GetByExcelName("ExcelField").Value = Import.Fields.GetByExcelName("K").Value + " " + Import.Fields.GetByExcelName("L").Value
2. In AfterDictionaries you could do something like the following:
Dim zipCode as string = Import.Fields.GetByExcelName("ZipCodeExcelField").Value
if (zipCode.Length > 0) and (zipCode.Length Import.Fields.GetByExcelName("ZipCodeExcelField").Value = zipCode.PadLeft(5,"0")
end if
PadLeft will append leading 0s to make the total length of the string 5 characters for the zipcode (in my example). Obviously zip+4 processing the logic will be different to account for the +4.
It seems you are trying to do everything in code. I think if you consider that you don't need to create all of these functions with computed columns when you can overwrite or write to all columns/fields in a profile at various events. Just as I did in the Zip Code example, above I take what the profile has for that row and IF I need to change that value I write it back to the same column with the new value. And based on your first question I think it is easier if you just create a Virtual Field in the Profile UI and just write directly to that field with the concatenated values.
I hope this helps.
Paul - One thing I meant to point out but did not is you would want to replace the values ZipCodeExcelField and ExcelField with the appropriate Excel Field Values: e.g. Z, AB, AD; so it should look something like Import.Fields.GetByExcelName("AB").Value
Paul Ulici
I'm new at this, but have done plenty of VB before.
In trying to import a file, I'm having to do a bit of manipulation on some of the fields.
let's just say 2 things at this point:
1) concatenate two fields into one (house number + street name) in order to compare to the street address field
2) pre-pend leading zeroes to the zipcode field since they get stripped out at some point.
So both of these are no biggie for API right?
I go add a virtual field,
Then go into code use the add virtual column button to drop a chunk of code in there, and modify it to do what I want. great
_
Public Function ComputedColumn_635845922937436970( _
ByVal oField as ImportOM.API.iField, _
ByVal Cancel As ImportOM.API.iCancel) As String
'This function must return a text value
Dim sReturn As String = Import.Fields.GetByExcelName("K").Value + " " + Import.Fields.GetByExcelName("L").Value
Return sReturn
End Function
Except at this point it doesn't work... nothing happens when testing code
so I go back to my virtual fields and try different things like naming my field the same as the function or selecting the function in the row for that Virtual field from the dropdown. Test again, nothing happens.
I notice that in one of the samples that works, someone had commented out the first line of code that says
That's odd, so I comment it out in my code as well, and remove the function name from the function column in the virtual fields view for that field.
Now it works. - that is ODD.
It seems like this sorta works by default. because there's only one function defined.
But what do I do if I want to add one more function, for example to append leading zeroes to a zipcode??
How do I tell it which virtual field does that function belong with?
Is there a video demo for that?