Drawings Transmittal

[Amorphous - Julian]

Hi @Nikolay Zhelyazkov 

 

I want to create a Document transmittal (drawing revision list) with a worksheet.

I can successfully call up the drawings with a certain 'Revision Number' (say '2A') in the 'Current Revision' with the formula: 

 

=IF(('Title Block Border'.'Current Revision Number')=('2B'), 'Y', 'N')

 

However, I cannot call up Revision Number that is NOT in relation to a titleblock's 'Current Revision' Number. 

For example, if I want the above formula to (instead of reporting on the 'Current' revision results) return any results that have ever had Revision Number '2A', how would I go about doing that?

 

Thanks. 

[Nikolay Zhelyazkov]

Hello @Amorphous - Julian,

 

You could use the formula:

 

=IF(('Title Block Revision Data'.'Number'='*A*'), 'Y', 'N')

 

Check the syntax to be sure there is no error on my side as I tested this with =DATABASE(INSYMBOL & INVIEWPORT & ('Title Block Revision Data'.'Number'='*A*'))

 

Also, this will get any revision number that has A in it, such as A1, AA, etc. To narrow down the results you could add one '\b' (blank) symbol as a suffix of the Number value, that way you will get only Revision Numbers ending with A. I have not added the blanks here because the web edditor is omitting them...

 

Let me know if you need further help.

 

Best Regards,

Nikolay Zhelyazkov

[Pat Stanford]

A little more background and another question:

 

It appears that as you add revisions, each revision is actually a duplicate record that is attached (Title Block Revision Data -1, Title Block Revision Data-2, etc.) and that the worksheet "automagically" combines these multiple records into single data return that data in a concatenated string (if you have Rev A, Rev B, Rev C, the actual returned value is CBA).

 

As long as you have a relatively small set of revisions this should work OK, but I see this breaking down with additional revisions. It will work OK to get Rev C2, but if you want to get only the items that have just Rev C (and not C1, C2, etc.) it will be more challgening.

 

I see an even bigger problem when you are using numbers for revisions and get over 20.  If the value returned is ...10111213...  the return will pick up the 12 followed by the 13 as 21.  Hopefully no-one actually has that many revisions, but it could be a problem.

 

There are a couple of potential work arounds for this using worksheet scripts, but that is more complicated than just using the built in worksheet functions.

 

@Nikolay Zhelyazkov Is the "automagical" concatenation of the Revision (and probably the Issue records as well) a special case in the worksheet? Or could I create my own records and add a dash number to then end and get the same functionality?

[Nikolay Zhelyazkov]

Hello @Pat Stanford,

 

10 minutes ago, Pat Stanford said:

Is the "automagical" concatenation of the Revision (and probably the Issue records as well) a special case in the worksheet? Or could I create my own records and add a dash number to then end and get the same functionality?

- The dublication of data is special case for Revision Data. It is caused by the old way revision data was stored - in the Title Block Revision Data record with concatenations with '\b' in between and the new way of using separate record for each revision for simpler extraction of revision data in worksheets. This "automagical" functionality is handled from the TBB, so it cannot be achieved by simply adding dashes to custom record names.

 

10 minutes ago, Pat Stanford said:

I see an even bigger problem when you are using numbers for revisions and get over 20.  If the value returned is ...10111213...  the return will pick up the 12 followed by the 13 as 21.  Hopefully no-one actually has that many revisions, but it could be a problem.

- if you add the blank symbol '\b' in the end of the desired Revision Number, like "Rev C'\b'", you will only get revision numbers ending with C. In regular expression words Revision Number = *C. So, depending on your numbering system, it should not be that problematic to get the desired unique results.

 

Just to clarify, this workflow that we are discussing now is simply a side effect caused by the old and new way revisions are working, it is not designed for this purpose.

 

Best Regards,

Nikolay Zhelyazkov

[Amorphous - Julian]

19 hours ago, Nikolay Zhelyazkov said:

=IF(('Title Block Revision Data'.'Number'='*A*'), 'Y', 'N')

 

Amazing!!! Completely solved the issue for me thanks @Nikolay Zhelyazkov

 

I don't complete understand what the two asterisk next to the A in *A* does, can you explain a little?

Also, what if I would like the '*A*' part of the formula to read the value of a particular cell in the worksheet, how would I go about pointing it to the value of a cell? 

Thanks again. 

Edited November 22, 2019 by Amorphous - Julian

[Nikolay Zhelyazkov]

1 hour ago, Amorphous - Julian said:

I don't complete understand what the two asterisk next to the A in *A* does, can you explain a little?

- This is a regular expression formula. For worksheets it means 0 or more characters. For example A* means any string starting with A, *A means any string ending with A and *A* means any string that has A somewhere in it.

 

1 hour ago, Amorphous - Julian said:

Also, what if I would like the '*A*' part of the formula to read the value of a particular cell in the worksheet, how would I go about pointing it to the value of a cell? 

- Not sure what are you trying to do this way. However, I suppose you could get the cell value and concat it with * in both ends so that you end up with this formula. If this is not what you are trying to do, could you send me test file or image of what is the desired behavior?