Solved

Dates in document macros

M
Rank
Userlevel 4
Badge +9

In IFS Cloud, I encountered a peculiar behavior of macro attributes of the type date.
The system has been set up in such a way, that in documents connected to the object CompanyPerson the employee's date of birth can be shown (core attribute Company_Person.Date_Of_Birth). In the document, however, the date shown is the actual birth date minus 1. E.g. when the employee's date of birth is 1 January 1990, the date shown in the document is 31 December 1989.
What could be the cause of this behavior and, more important, how can it be solved?

Thanks in advance for your reactions.

icon

Best answer by Mathias Dahl 16 June 2022, 21:29

View original

11 replies

Amila Samarasinghe
Rank
Userlevel 6
Badge +14

Hi @Martin Hulsenboom 

 

That is a very strange case indeed. Would you mind sharing the macro code here?

 

Thanks

Amila S. - https://www.linkedin.com/in/amila-samarasinghe-b3285974/
M
Rank
Userlevel 4
Badge +9

Hi @Amila Samarasinghe,

It's macro code from the Ventechi environment:

 

IFS_01_08_OBJECT_CONNECTIONS

 

PUBLIC FUNCTION OBJECT_CONNECTIONS(ATTR)

'ON ERROR RESUME NEXT

 

    NROWNO = 1

    NTABLEROWNO = 2

    SLUNAMES = ATTRVALUE(ATTR)

    SARRAYLUNAME = SPLIT(SLUNAMES, ";", -1, 1)

    FOR EACH SLU IN SARRAYLUNAME

        NKEY = ""

        NCOUNTER = 0

        NROWNO = NROWNO + 1

        STAG = SLU & ".COUNT"

        NCOUNTER = ATTRVALUE(STAG)

        NROWNO = NROWNO + 1

        STAG = SLU & ".ATTRIBUTES"

        SATTRIBUTES = ATTRVALUE(STAG)

        SARRAYATTRIBUTE = SPLIT(SATTRIBUTES, ",", -1, 1)

        IF NCOUNTER > 0 THEN

            FOR NRECCOUNT=1 TO NCOUNTER

                FOR EACH SATTRIBUTE IN SARRAYATTRIBUTE

                    STAG = SLU & "." & NRECCOUNT & "." & SATTRIBUTE

 

                    NROWNO = NROWNO + 1

                    IF O_APP = "Microsoft Word" THEN

                       IF LEN(ATTRVALUE(STAG)) > 254 THEN

                          N = 0

                          FOR I = 1 to LEN(ATTRVALUE(STAG)) STEP 254

                             IF N > 0 THEN

                               O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG & "_" & N, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             ELSE

               O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             END IF

                                 N = N + 1

                                 NEXT

                       ELSE

IF LEN(ATTRVALUE(STAG)) = 19 AND MID(ATTRVALUE(STAG),5,1) = "-" AND MID(ATTRVALUE(STAG),8,1) = "-" AND MID(ATTRVALUE(STAG),11,1) = "-" AND MID(ATTRVALUE(STAG),14,1) = "." AND MID(ATTRVALUE(STAG),17,1) = "." THEN

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3",  MID(ATTRVALUE(STAG),1,10)

ELSE

 

                          O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  ATTRVALUE(STAG)

END IF

                       END IF

                    END IF

                    IF O_APP = "Microsoft Excel" THEN

                       IF LEN(ATTRVALUE(STAG)) > 254 THEN

                          N = 0

                          FOR I = 1 to LEN(ATTRVALUE(STAG)) STEP 254

                             IF N > 0 THEN

                                              OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG & "_" & N, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             ELSE

                                              OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  MID(ATTRVALUE(STAG),I,254)

                             END IF

                                 N = N + 1

                                 NEXT

                       ELSE

                          OBJWORKBOOK.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "4",  ATTRVALUE(STAG)

                       END IF                

                    END IF

                NEXT

                NTABLEROWNO = NTABLEROWNO + 1

                NKEY = ""

            NEXT

        END IF

    NEXT

    NROWNO = NROWNO + 1

 

END FUNCTION

 

PUBLIC FUNCTION ATTRVALUE(SVAL)

ON ERROR RESUME NEXT

    ATTRVALUE = SCRIPTVALUES.ITEM(UCASE(SVAL)).VALUE

END FUNCTION

Mathias Dahl
Rank
Userlevel 7
Badge +30

Add a message box before this code line:

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3", MID(ATTRVALUE(STAG),1,10)

The message box should display this:

MID(ATTRVALUE(STAG),1,10)

Then we will see if the value as extracted from the macro is wrong or if Word is doing something with the value.

I suspect the value is already wrong when the macro gets it, but that remains to be seen.

Which attribute did you pick in the basic data for macro attributes?

 

/Mathias
M
Rank
Userlevel 4
Badge +9

Hi Mathias,

Thanks for your reaction. I have added the code you mentioned, but I cannot see a message. What I do notice, is that the system doesn't automatically open Word anymore. When I open the document manually, I still see the wrong values.

As for the attributes: I have kept it very simple. It was a first test in order to give the customer an impression of what we are able to. Note, that I have defined two date fields: a custom field (the jubilee hire date) and a core field (birth date). In both cases, the shown values are wrong.

 

M
Rank
Userlevel 4
Badge +9

@Mathias Dahl , And to verify what I've done in the code itself:

 

Mathias Dahl
Rank
Userlevel 7
Badge +30

Hi,

I know what the problem is. My mistake. I didn't not tell you how to add a message box, just what it should output. 

You need to wrap what you added there in a call to MsgBox(...). Should be something like this (untested, typing this on my phone):

MsgBox(MID(ATTRVALUE(STAG),1,10))

I recommend to have a developer as part of the implementation/project team if you plan to produce new macros, or even work with existing ones. Macros are not officially a customization, but it does involve writing code.

 

 

/Mathias
M
Rank
Userlevel 4
Badge +9

 

​Hi @Mathias Dahl , The message is showing the correct date… So, this implies that it should be something in Word?
Best regards,
Martin

Mathias Dahl
Rank
Userlevel 7
Badge +30

Good finding!

I have a crazy idea for you to try. Not tested and not thought through...

I noticed something about the code that handles that separate case:

O_APP.ACTIVEDOCUMENT.CUSTOMDOCUMENTPROPERTIES.ADD "IFS_" & STAG, FALSE, "3",  MID(ATTRVALUE(STAG),1,10)

See the "3" there? Other properties are added with "4". My guess is that "3" might mean that the property is a date and not a string. See what happens if you change it to "4"…

 

/Mathias
Mathias Dahl
Rank
Userlevel 7
Badge +30

Seems I was right :) 

The third parameter there is the type and these are the ones available:

https://docs.microsoft.com/en-us/office/vba/api/office.msodocproperties

As you can see, the number 3 stands for a date.

So, Word is probably interpreting the value we give it in the wrong way, or we give it on the wrong format.

 

/Mathias
M
Rank
Userlevel 4
Badge +9

Hi @Mathias Dahl,

Your suggestion proved successful. I have changed the 3 into 4, and when testing the document macro again, the dates were shown correctly!

Thanks, Mathias!

/Martin

Mathias Dahl
Rank
Userlevel 7
Badge +30

Great 😃 , thanks for reporting back!
 

/Mathias

Reply


Terms & ConditionsCookie settings