<< Click to Display Table of Contents >> Navigation:  Drawings > Drawing Title Blocks > Variant notes querying a database

Variant notes querying a database and useful macros

In MechworksPDM variant notes you have the possibility to insert into variant notes the results of database queries. As a simple example you can insert the value of a given field related either to the drawing itself or to the document contained but also perform complex queries or connect to external data sources to display information from mixed sources.

This introduces three levels of growing complexity that in this page are handled separately:

Insert a field value

You can insert the content of any field related either to the drawing itself or to the contained document. The dialog for entering the variant notes allows you to select a field by double-clicking an element in one of the two lists. For more information on how to enter graphically the value of a field, read Adding variant notes to a drawing template.

If you want to explicitly indicate a field value as a simple SQL query, a sample line requesting the field description for the drawing is @DBW=DRAWING.DESCRIPTION, while a sample line requesting the field description for the contained document is @DBW=DOCUMENT.DESCRIPTION

Formatting expressions

It is possible to optionally define formatting expressions at the begin of a Variant Note definition as follows:

@DBW=[@<formatting expressions>@]<Variant Note definition>

where:

<formatting expressions> <formatting expression>[,<formatting expression>[,...]]

<formatting expression> <formatting identifier>=<value>

<formatting identifiers> uppercase | lowercase | maxlen

Example

given a standard variant note definition:

@DBW=DRAWING.DESCRIPTION

the following alternate definitions will format the value in a different ways:

@DBW=@[email protected]

makes the Variant Note upper case

@DBW=@[email protected]

makes the Variant Note lower case

@DBW=@maxlen=20,[email protected]

wrap the variant notes to the first 20 characters and makes it upper case

 

see also General→Drawings→[dd-MM-yyyy] Date format for variant texts

Insert the result of a query

You can query the MechworksPDM database in order to retrieve the result of a specific query. Let's see how starting from a simple example: you retrieve the content of the field DESCRIPTION  for the contained document.

You may have written @DBW=DOCUMENT.DESCRIPTION just as well as a special shortcut created for such simple requests.

Each query starts with the standard clause @DBW= after which comes the real sql query script. You need to specify always both the field and the table. If you need to relate the query to the current drawing or to the current document, always use the field UNIQUE_ID to identify the correct record as in the example.

@DBW=SELECT DESCRIPTION FROM DOCUMENT WHERE UNIQUE_ID = $(DOCUMENT.UNIQUE_ID)

The symbol $() is used by MechworksPDM to indicate a field value of the current document or of the current drawing.

The macro $(SYSUSERNAME) expands in a SQL query as the current user name as in the following examples:

Queries all the documents created by the current user

.SQL

.KEY 0
SELECT ID,DESCRIPTION FROM DOCUMENT WHERE CREATED_BY='$(SYSUSERNAME)'

Queries all the project assigned to the current user with DBWARM

.SQL

.KEY 0
SELECT ID,DESCRIPTION FROM PROJECT WHERE ID IN ( SELECT PROJECT_ID FROM DBW_PROJECT_USER WHERE USER_ID='$(SYSUSERNAME)') ORDER BY ID

As a fourth example, we get the third revision for the contained document.

@DBW=SELECT REVISION FROM REVISIONS

WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID)
AND REVISION='3'

A query returning no values will not fill the note.

If you need to know the name of the standard fields in the MechworksPDM database, you can consult the file DBWorks\DBWorks.msg which contains all localized definitions, including table and field names.

Connect to external data sources for querying them

If you want to write a SQL script that queries another data source available via ODBC, you need to specify the source before the SELECT statement as follows: if your data source is OTHER_DATA_SOURCE, then your query will start with @DBW=(OTHER_DATA_SOURCE)SELECT ...

The general format is @DBW=(<data source name>)SELECT ...

As an effect, MechworksPDM executes the SELECT statement on the ODBC data source available as OTHER_DATA_SOURCE in the ODBC panel.

Example: Inserting a table of revisions using variant texts

Suppose you want to create a revision table in your drawing, obtaining something like:

In such case you must create 5 x 3 = 15 variant texts.

For the row n.1 the three SQL queries are defined as follows:

@DBW=SELECT REVISION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='1'

@DBW=SELECT DESCRIPTION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='1'
@DBW=SELECT CREATION_DATE FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='1'

For the row n.2:

@DBW=SELECT REVISION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='2'

@DBW=SELECT DESCRIPTION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='2'
@DBW=SELECT CREATION_DATE FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='2'

For the row n.3:

@DBW=SELECT REVISION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='3'

@DBW=SELECT DESCRIPTION FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='3'
@DBW=SELECT CREATION_DATE FROM REVISIONS WHERE DOCUMENT_UNIQUE_ID=$(DOCUMENT.UNIQUE_ID) AND REVISION='3'
....

As you can see it you impose the revision at the end of the SELECT statement; if no revisions were yet created, an empty text will be placed instead.

If you want to embed programmatically an Excel table of revisions inside a drawing, you can use the similar script demo_lst/Embed_revisions_excel_sheet.LST

Insert last revision information ($LASTREV)

This macro speeds up the input, in drawing template, of value from the last revision record.

It must be put in the variant note as

               @DBW=SELECT $LASTREV(<field name of the REVISION table>)

and it shows in the drawing the value related to he specified field in the record of the last revision for the current drawing being saved or updated.

Example

Suppose that you want to show in a drawing 4 values related to the last revision of the same drawing:

REVISION
APPROVED_BY
APPROVAL_DATE
NOTES

To retrieve info you have to put 4 variant notes as follows:

@DBW=SELECT $LASTREV(REVISION)

@DBW=SELECT $LASTREV(APPROVED_BY)
@DBW=SELECT $LASTREV(APPROVAL_DATE)
@DBW=SELECT $LASTREV(NOTES)

It is also possible to insert in the drawing values from the revision records of the DOCUMENT being drawn by using the macro $LASTDOCREV(<name of a field of the REVISIONS table>).
This is very useful when in Link Mode, since the approval operation must be started from the document side.
A sample of the usage of this macro is the following variant text:

@DBW=SELECT $LASTDOCREV(NOTES)

This variant note puts in the drawing the value of the field NOTES of the record of the last revision of the drawn DOCUMENT (part or assembly).

Insert current revision information of the document ($CURRDOCREV)

The macro $CURRDOCREV(<revision table field name>) returns the value of a field in the REVISION table for the current working-in-progress revision of the document.

The difference with the $LASTDOCREV is that the $LASTDOCREV return the value of the field in the last approved revision, while the $CURRDOCREV returns the value of the field in the current checked-out/checked-in state.

Of course, if a record is approved, the two macros will return the same value.

Example

Suppose Part1 has a drawing Draw1 and both are in checked-in state, with rev number 5; the description of rev number 4 for the document was: "Added fillet", the description of the new rev number 5 is: "The fillet was too big"

@DBW=SELECT $CURRDOCREV(DESCRIPTION) returns "The fillet was too big"

@DBW=SELECT $LASTDOCREV(DESCRIPTION) returns "Added fillet"

Insert current revision information of the drawing ($CURRREV)

This macro returns the value of a field in the REVISION table for the current work-in-progress revision of the drawing.
The difference with the $LASTREV is that the $LASTREV returns the value of the field in the last approved revision, while the $CURRREV returns the value of the field in the current checked-out/checked-in state.
Of course, if a record is approved, the two macros will return the same value.

Example

Suppose drawing Draw1 is in checked-in state, with rev number 5; the description of rev number 4 for the drawing was: "Added fillet", the description of the new rev number 5 is: "The fillet was too big"

@DBW=SELECT $CURRREV(DESCRIPTION)
returns "The fillet was too big"

@DBW=SELECT $LASTREV(DESCRIPTION)

returns "Added fillet"

Insert first revision information of the document ($FIRSTREV)

This macro returns the value of a field in the REVISION table for the first revision of the document.

Example

@DBW=SELECT $FIRSTREV(DESCRIPTION)