Trust ID PBCEX logo 300x102

VB scripts and keys

10 years 1 week ago
richardbeal
Fresh Boarder
Fresh Boarder
Posts: 9
More
Topic Author
VB scripts and keys #1881
We have a source table with a key: StaffID.
We have a destination table with a key: IPSUserID (integer) and a unique but non key: StaffID

The VB script takes in the StaffID, checks if it exists in the dest table if so give the IPSUserID if not then provides a new IPSUserID which is the last no plus 1.

So adding a new record via CX adds a record in the source table.
Printing it will successfully add the record in the destination table with the incremented IPSUserID.
>> Reprinting the card will result in an error of inserting a record with the same IPSUserID.

How do I set this up ?
1. Use Dest table with StaffID as the key. Make IPSUserId as a storage field, StaffID as the key and use the function to generate it. Doesn't work as above..
2. Use Dest table with StaffID as the key. Make IPSUserId as a storage field, IPSUserId as the key and use the function to generate it. Doesn't work as above..
10 years 1 week ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
VB scripts and keys #1882
Hi Richard,

I'm not sure if i understand it completely, but my first thought would be that you do not nee a .NET function at all. If you define a storage item on the destination table and select StaffID as key, then CardExchange will check whether a record exists with that StaffID upon executing the storage item. If so, the record will be updated and if not, the record will be created. The database should take care of generating a new value for IPSUserID when a record is inserted.

Now, if you need the IPSUserID already before printing the card, for example to use it for a mapping, the above will not work and you do need the .NET function you describe. If the function correctly checks whether the record exists and only generates a new record when that is not the case, the function can be called as many times as you want without causing an error, and always yielding the same result for a particular StaffID. For using the IPSUserID in the mappings, you should always refer to the .NET function output and not read it directly from the database, as you do not know whether it already exists.

The disadvantage of all this is that you cannot control when and how often the function is executed, so it is very important that the function is robust and does not cause trouble when called repeatedly.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
10 years 1 week ago
richardbeal
Fresh Boarder
Fresh Boarder
Posts: 9
More
Topic Author
VB scripts and keys #1883
Hi Rutger,
thanks again for your speedy reply. The dest table doesn't auto generate the IPSUserID, so we cant use option 1. Thats why we must use the vb function.

Option 2: How should I do the storage mapping ?

a. Define the key as IPSUserID but then say Supply value For Primary Key. Then with storage key mapping use the vb function.

b. Define a field called IPSUserID and Use Primary key as selected Name. Then with storage mapping use the vb function.

Both these methods dont appear to work.
10 years 1 week ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
VB scripts and keys #1884
Hi Richard,

The option a sounds fine to me, but you will need to test that thoroughly as it is a rather unusual construct. Most probably nobody has done something like that before. (I do not understand what you suggest as option b).

The Ultimate edition has also the possibility to control the .NET function execution time by defining a virtual plug-in. This works as follows. Maybe it helps you in this situation
  • You add a plug-in, but in the DLL box instead of selecting a regular plug-in you type "void" (or whatever). You evaluate the void plug-in when printing before the card enters into the printer.
  • CardExchange now creates a void plug-in with one input and one output parameter. Map the output parameter to the Database-key item and the ,NET function to the input parameter.
  • The void plug-in will just copy the .NET function result to the database-key item, but the construct is useful because it allows you to control when the .NET function is executed.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)