Recent Posts

When Filtering using the *Time Filter on a DateTime field in Oracle connections there can be errors. Changing the SQL DataType of the field to Varchar2 and the VB DataType to VbString and it should work ok.

I should add that this code should be taken out for Released software where you intend to use the DataLink with EIF Services.
EIF Services converts the VB in Control Tag Scripts to .NET before running them and the Objects used to write to the file aren't available in .NET so the DataLink will break.

Debugging vbscript in datalinks can be hard. I've implemented a logging function that lets you log into a file. This function is turned on by either setting a User Option ( Vision Setup - Profile - Products - Options ) or by logging in with Logging enabled ( on the Options tab of the login Window ). Each output to the log has a timestamp. Put this code at the start of your main Control Tag script. It will create a Control Tag showing if logging is enabled which is then used to switch out the logging instructions if they aren't used - so there is no performance overhead in having logging code in your datalink. The logfile is located in your Temp folder and is called Captains_Log_ with a timestamp on the end

An example of logging some diagnostic information -

     {TagLogging=1}
       Log_Message "C_Name " + CStr(C_Name) + "    C_Server " + CStr(C_Server) + "    C_Database " + CStr(C_Database)
     {TagLoggingEnd}


/*   CODE STARTS HERE   */


select 'UserOption03',0,'Logging Enable - if set ignores Vision Logging - Captains_Log in %temp%'
{OracleStart}From Dual{OracleEnd}
GO


/*VBSCRIPT=10000 GLOBAL*/
dim WshShell
dim tempdir
dim objFSO
dim objLogFile
dim LogFileName
Dim objLog
Dim LsEngine_info
Dim Logging_Enabled

' Extract the location of the System temporary folder ( same place Vision puts its logfiles

Set WshShell = CreateObject("WScript.Shell")
tempdir = WshShell.ExpandEnvironmentStrings("%temp%")
LogFileName = "Captains_Log_" + cstr(year(now)) + right( "00" + cstr(month(now)),2) + right( "00" + cstr(day(now)),2)+ right( "00" + cstr(hour(now)),2)+ right( "00" + cstr(minute(now)),2)

Set objFSO = CreateObject("Scripting.FileSystemObject")

{TagUserOption03=1}
   If 1 = 1 then
{TagUserOption03Else}
If objFSO.FileExists(tempdir+"\LsEngine.log") OR  {TagUserOption03} = 1 Then
   Set objLog = objFSO.GetFile(tempdir+"\LsEngine.log")
   LsEngine_info = objLog.DateLastModified
   If Mid(LogFileName,14,4) = Mid(LsEngine_info,7,4) AND Mid(LogFileName,18,2) = Mid(LsEngine_info,4,2) AND Mid(LogFileName,20,2) = Mid(LsEngine_info,1,2) Then
{TagUserOption03End}

      Log_Message "############################################# Logging Started  #############################"
      Log_Message "###" + cstr(LogFileName) +"###"

      Log_Message "{FilterKey1ProductAuthorProductRegistration} "
      Log_Message "{FilterKey1ProductCode} "
      Log_Message "{FilterKey1ProductDescription}"
      Log_Message "{FilterKey1ProductVersion}"
      Log_Message "{FilterKey1ProductDemoMode}"
      Log_Message "UserOption01 = {TagUSerOption01}"
      Log_Message "UserOption02 = {TagUSerOption02}"
      Log_Message "UserOption03 = {TagUSerOption03}"
      Logging_Enabled = 1
      oAlchemyControlTags.ADD "Logging","1"
   Else
      Logging_Enabled = 0
      oAlchemyControlTags.ADD "Logging","0"
End If
{TagUserOption03=0}
Else
   Logging_Enabled = 0
   oAlchemyControlTags.ADD "Logging","0"
End If
{TagUserOption03End}

Sub Log_Message ( Text_Message )
Set objLogFile = objFSO.OpenTextFile(cstr(tempdir) +"\" + cstr(LogFileName) +".log", 8, True)
objLogFile.Writeline Date & " " & Time & "  " & Text_Message


objLogFile.Close


end sub

GO

another solution may be to just includel the TableModifer script at the end of your superfield script id the Table Modifier Filter has been set up.

replaced message with typo's corrected ( hopefully )

Hi Baptiste
Superfield Control Tag Scripts are run whenever the Superfield is changed. Table Modifier scripts are run whenever the Table modifier is changed.
If you want to have tags that use information from both then the tags will have to be created within the Superfield script. This example is very simplistic but I hope its ok.
For example
Superfield sSuper has values A,B,C
Table Modifier tTM has values X,Y,Z
If you want a Control Tag that is going to be based on both - {Tag_{FilterKey1sSuper}_{FilterKey1tTM}} then all possible variations for a given Superfield will have to be created within the Superfield ControlTag Script.
Select '_{FilterKey1sSuper}_X},'abcdef'
GO
Select '_{FilterKey1sSuper}_Y},'abcdef'
GO
Select '_{FilterKey1sSuper}_Z},'abcdef'
GO

I hope this answers your question.
cheers
Richard

Hi,

I've a problem on a refresh of a control tag.
I've a vbscript which is execute on a table modifier, I ve already a superfield for my companies which is execute before the table modifier. This superfield has a control tag ({TagBddNom{FilterKey1sF_Societe}}) is run well but when I change the FilterKey of my table modifier the vbscript isn't execute a second time.
What can I do to refresh all the time this script?

Regards,

/*VBSCRIPT*/

'oAlchemyControlTags.ConnectionPoolIndexSet 0,False

Dim adoCon
Dim rsFields

set adoCon = oConnection.ConnectionObject(CInt(ProductConIndex))
set rsFields = adoCon.Execute("SELECT E1.code AS AxeCode, E2.numero AS Numero, E3.code AS PlanCode, E3.Caption AS PlanLibelle FROM {TagBddNom{FilterKey1sF_Societe}}.TESPACEANALYTIQUE E1 LEFT OUTER JOIN {TagBddNom{FilterKey1sF_Societe}}.TCONSTITUTIONESPACE E2 ON E2.oidEspaceAnalytique = E1.oid LEFT OUTER JOIN {TagBddNom{FilterKey1sF_Societe}}.TDIMENSIONANALYTIQUE E3 ON E3.oid = E2.oidDimensionAnalytique WHERE E1.code = '{FilterKey1tM_Axe}' ")

Do Until rsFields.EOF
    If not isnull(rsFields.Fields("PlanCode").Value) THEN oAlchemyControlTags.Add "{FilterKey1sF_Societe}Axe{FilterKey1tM_Axe}CodePlan" + Cstr(rsFields.Fields("numero").Value), rsFields.Fields("PlanCode").Value
    If not isnull(rsFields.Fields("PlanLibelle").Value) THEN oAlchemyControlTags.Add "{FilterKey1sF_Societe}Axe{FilterKey1tM_Axe}LibellePlan" + Cstr(rsFields.Fields("numero").Value), rsFields.Fields("PlanLibelle").Value
    rsFields.MoveNext
Loop

GO

Hi
Sorry for not  replying sooner but this site is only meant to be used for reference info and problems are meant to be taken up with Support directly. I'm only monitoring this site from time to time.
It sounds like there are problems in the code to create your virtual tables. I'd suggest turning on logging to check whats happening ( 3rd tab on login window will create an lsEngine.log file in your temp directory which will provide a lot more info on whats going on.
If you can't solve the problem with that then I suggest talking to your Account Manager about getting additional training or consultancy.
cheers
Richard

Hi Baptiste

You can use that function or the Add one detailed below. This is taken from an extension datalink for SSPR over SunSystems. There is some vbscript which handles user defined tables and automatically adds joins in to main tables. It looks a bit scarey but thats because the Join has quite a few control tags in which are specific to Sun5.
 Basically I'm giving it a Join Alias ( *n , where n is the counter of joins to that tables from  the current one , if you've only one then its *1, if you have 2 then the first is *1 and the second is *2 )
KZ is the id of the table being joined to
Purchase Order Header is the Descriptin for the Join
Join is the field type
0 is the field length
the rest is the actual join

oAlchemyControlTags.DefinitionModifyTableFieldAdd "*" & cstr(joincount), "KZ", "Purchase Order Header","","Join", 0, , , , , "LEFT OUTER JOIN {TagTablePrefix::{FilterKey1DbC}}_PO <+[id2]+> ON <+[id2]+>.PO_TXN_REF = <+[id1]+>.PURCHASEORDERREFERENCE AND <+[id1]+>.BUSINESSUNITCODE = '<+[FilterKey1DbC]+>'"

Hi,

Is it possible to insert a join item in a table with the function : oAlchemyControlTags.DefinitionModifyTableFieldInsert?

If not, which fonction I've to use to add this item in a table?

Regards,

Hi All,

I am running into a situation which i could not try the solution. Appreciate anyone help me resolve it as it is urgent for me.
I developed a Serduct project for oracle database. In Serduct studio, I created a virtual table to consolidate data from other tables. Because of coming from multiple tables, the query to create virtual table is long.
After compiling and serializing Serduct, I wrote reports based on virtual table.
However, I can run the report only the first time after log in to Vision. The second time it always shows error messages as "End of file communication" or "Unspecified Error". I look at the query generated and discover that the generated query was "cut" the rest.
If i log off and log in again, still can run only the first time.
Is there any idea on my issue?

Thanks,