Record formats and a database

[RDS Casa]

Does any one know if you can get one work sheet to reference another?

Here is my problem. I have symbol which carries a record format. It has a text string which is linked to a field in that record format. I have a table which in one column lists out all the occurrences of that field.

I need the second column to return text string dependent on the first column, with that text string stored in another worksheet.

Hopefully this is not as complicated as it sounds. Basically I just need one worksheet to look at the data in one column, match it with a column in another worksheet, and return the data in the adjacent column in the first works sheet?

Anyone follow this?

Thanks

[Bryan G.]

Yes, you can reference another worksheet.

[RDS Casa]

Thanks

I've got one worksheet to look at another but only in the simplest way that it returns a single cell.

Say I have 2 worksheets X & Y

Work sheet X has a number of codes, eg F1.352 in Column A, and a text string eg. "dense conc block" in column B.

Work sheet Y has a list of a few codes in column A (driven by record formats, but that shouldn't matter) that match the codes in worksheet X column A

How do I get Worksheet Y to return the text from X column B to match the codes in column A

i.e X essentially drives Y

Is this clear? probably not. Sorry

[Bryan G.]

Rob, I think I know what you are looking for. I have to run now to pick my oldest up from camp. I will work something up this afternoon and post it for you.

Hope this helps.

[RDS Casa]

That would be great. Thanks

[Pat Stanford]

I may be wrong, but I don't this this is possible with the VW Worksheets.

In Excel you would use something like the Lookup function to do this. Don't think there is an equivalent in VW.

Depending on how often your data changes, it may be possible to write a Vectorscript that would get the correct data/link and put it in the cell, but you would have to remember to manually run the script whenever the data in either worksheet changes and you need the worksheets "correct"

[RDS Casa]

I think it is the Vlookup command in excel if this helps. Does anything similar exist in VW?

http://msgroups.net/microsoft.public.excel.worksheet.functions/compare-1-cell-to/105249

Thanks

[RDS Casa]

Thanks Pat, I just saw your reply after I posted the above.

[RDS Casa]

Running the script manually would be ok. Its not likely the data would change very often in this situation.

As much as I want to get into script writing, I've always failed to get anywhere before, so any help to get me started would be gratefully received

Thanks

[RDS Casa]

Pat,

I?ve had a look through the forum, it appears you might be the man when it comes to VS and works sheets? So, although I feel I have learned much from your ?Worksheet of PIO Fields? script, and can just about gauge what each line of code is doing, I?m struggling when I depart your code.

I'm not a coder - you'll probably notice this quite quickly.

When I run the script I want it to:

1. Create a worksheet, which is named according to the current layer combined with ?SpecRefKey?.

2. Lists out all the field values present of a given field within a given record (with no duplications, should that filed value appear twice).

3. The script also references those field values against another existing worksheet, and returns a string according to the value.

I think I?m close to 1.

Not sure about 2. hopefully halfway?

Miles from 3.

Should I be creating a spread sheet for 2? Or should I really look to create a database/report that lists the occurrences of the field values. I have created one of these manually in the file, but as you suggest, I need a script to do a lookup?

The poor attempt at a script below tries to create a spread sheet, rather than a data base?

-----------

Procedure CreateSpecRefKey;

LayerName : Handle;

occur : Integer;

WSName :String;

WSH :Handle;

Begin

LayerName := ActLayer; {defines active layer as a string?}

occur :={I need to count the number of unique occurrences of a given field??)

WSH := CreateWS(Concat(LayerName,' SpecRefKey'),occur+1,2) {creates the worksheet with 2 columns and a variable number of rows dependent on how many occurrences of the field there are plus 1?}

SetWSPlacement(WSH,200,200,800,700); {I?ll finalize this latter}

SetWSColumnWidth(WSH,1,1,200);

SetWSColumnWidth(WSH,2,2,200);

SetWSCellFormula(WSH,1,1,1,1, 'Spec Ref');

SetWSCellFormula(WSH,1,2,1,2,'Discription');

{I think this sets up my worksheet?}

This is where I get a bit lost. Very lost actually.

Should I keep going with this strategy? Your script appears to populate the cells using a "while" loop? Until one of the looping handles is more than the number of records? I think I see what?s happening, I just wanted to know if a similar strategy would work in my case, or if I'm going in the wrong direction already?

Thanks

[RDS Casa]

Hi

I gave up with this some time ago. Got completely stuck. Recently I tried again with a different approach and may have got further. I'm linking a excel spread sheet to the vectorworks file using ODBC compatibility. I followed this

http://kbase.vectorworks.net/questions/967/ODBC+Compatibility+-+Excel

So now I have a simple 2 column spread sheet (in excel). I have created a little symbol with text strings linked to fields in a record. The record is linked to the excel document through ODBC.

If I manually connect each symbol to a given row in the excel sheet, sure enough it can pull data in from the table to populate the other field, (and displays in the symbol).

But with the number of entries I have this is cumbersome.

Is there a way to put in a value in an id record format (say through the data tab) and then when I update it, VW pulls through the remaining data from the table. This would be like the Vlook up command, only it would externally reference a database

Or

Maybe I try a simple script that automates the "connect to database" and updates. this seems achievable. But with 100s of items, I don't want to scroll through a big list to find it. ideally I'd start typing in the id and it would cut down the choices or something.

Does anyone think there is further mileage in this approach, or have I hit another wall

Thanks

[Bob Holtzmann]

I've had success making schedules for doors and finishes, and simply referencing the schedule's layer into another Vw file. I wanted to do this because the floor plan's data and the schedules could work two ways only if they were in the same file.

A possible cheat would be to have two separate schedules referenced into a file, make viewports of each on a sheet, and use cropping to align them side by side.

[RDS Casa]

Thanks for the reply. Great to know people are out there.

I'm actually trying to achieve something like the attached jpeg image. Its simply a call out, where you put in a specification reference, then it looks up on a table the name of the item and displays it. A good time saver we thought. Obviously we will not be using foodstuffs to build with, but you get the idea.

The original post started with a simpler version, where the call out just contained the spec reference, then a key on the side of the drawing listed all the flags and put a spec item against the references listed. Different flags appear on different detail drawings, and the list was way too long to put the same key on every drawing.

anyway, with out a Vlookup function like excel, this proved impossible without scripting. And scripting is has just too involved to break into for me right now.

So, referencing an external data base seems like it might get us closer, its just a shame you have to link an object to the data base row, rather than putting in say a field value, and it then reference the object to the correct row.

If there is a simpler way to achieve this, please let me know.

[RDS Casa]

Ok, the jpeg did not attach. Try this one?

Thanks again