Browsing TP90_Data.tpd

[Andy McKenna]

Is it possible to browse this file with a SQL CE tool? I'd like to see the structure so I know what I can do with ExecuteSQL events. When I tried to open it, it wanted a password for the file.

 

[Howard Heckman III]

Hi Andy,

No, it is not possible to navigate the table structure of the TracerPlus database. However, take a look at this knowledge base article on writing SQL statements in TracerPlus.

https://support.tracerplus.com/index.php?/Knowledgebase/Article/View/216/0/writing-sql-queries-for-tracerplus

It should show you all the table and field names required to successfully write any SQL you'd like.

Let us know if you have any questions on this.

 

[Andy McKenna]

Are the choices for a dropdown stored in the .tsc file, then? I was hoping to be able to overwrite it when the form initializes by doing a select/group by on the table

 

[James]

TracerPlus Desktop deploys the dropdown values into a text file that is automatically imported when TracerPlus starts. The file is named S##F##_auto.txt, with the appropriate session and field numbers for the dropdown control.

One method I tried was to create a holder session and use sql to insert the data I wanted, then a live grid to display records from the holder session. It's caused a few problems with refreshing controls and timing - see my reply to your other post.

 

[Howard Heckman III]

Hi Andy,

The drop down data is stored in tables as well. Here is the table definition for the drop downs (For session 1):

Tablename: Session1_DD
Field list: Field_Index, DisplayValue, FilterValue, DBValue

Let us know if this helps.

 

[Andy McKenna]

Hi Andy,

The drop down data is stored in tables as well. Here is the table definition for the drop downs (For session 1):

Tablename: Session1_DD
Field list: Field_Index, DisplayValue, FilterValue, DBValue

Let us know if this helps.

I can't get that to do anything. Is there some way to see the output or errors from ExecuteSQL lines? I have no indication that anything is happening. Even if I try to delete the entire table, the value is still there in the dropdown.

 

[Howard Heckman III]

After modifying the drop down you may need to force a refresh of the drop down contents, either by a filter refresh or closing and reopening the form. Also, if you want to post your SQL we can try it out over here to see wha t we can find.

 

[Andy McKenna]

Closing and reopening the form doesn't change anything. Here is my SQL (tried different variations):

INSERT INTO Session1_DD (Field_Index, DisplayValue, FilterValue, DBValue)SELECT 1, 'Test2', 'Test2', 'Test2'

INSERT INTO Session1_DD (Field_Index, DisplayValue, FilterValue, DBValue) VALUES (1, '600721', '600721', '600721')

INSERT INTO Session1_DD (Field_Index, DisplayValue, FilterValue, DBValue)SELECT 1, Field[*{1}*], Field[*{1}*], Field[*{1}*] FROM Session1 GROUP BY Field1 ORDER BY Field1

 

[Howard Heckman III]

The Field_Index field is a varchar, so the value needs to be wrapped in single quotes.

Sorry should have given you the data types as well

 

[Andy McKenna]

No luck, kinda makes sense though because even when I was just deleting the entire table I couldn't make the dropdown empty out.

 

[James]

Howard, I'm also unable to get this to work. Can you post a sample that should inject a value into a dropdown that is the first field of session 1? I've tried:
INSERT INTO Session1_DD(Field_Index, DisplayValue, FilterValue, DBValue) VALUES ('1', 'test', '', '');
Also tried replacing the field names with Field1,... and replacing DD with F01 and Field1, with and without underscore. I've used messages attached to duplicate events to make sure the events are firing - one to execute the statement, and another to refresh the control.

 

[OliverR]

I'd also be interested in getting this to work. Dynamically adding values to dropdowns - without having to use Connect - has been a much requested feature and could open up a lot of possibilities.

I'm guessing the problem lies with "Session1_DD". What if you have several dropdowns in a single session? Do they all have their own table, and if so, how are they named?

 

[Andy McKenna]

I'd also be interested in getting this to work. Dynamically adding values to dropdowns - without having to use Connect - has been a much requested feature and could open up a lot of possibilities.

I'm guessing the problem lies with "Session1_DD". What if you have several dropdowns in a single session? Do they all have their own table, and if so, how are they named?

Assuming the structure is accurate, you would have the 1 table but filter on Field_Index

 

[OliverR]

Assuming the structure is accurate, you would have the 1 table but filter on Field_Index

Ah, of course, that makes sense.

 

[James]

Assuming the structure is accurate, you would have the 1 table but filter on Field_Index

Still no love with 0-indexed or 1-indexed values. I'd really like to see an actual sample statement from Howard or someone else at PTS. Right now this is looking like a table that exists but can't be accessed by us.

 

[Howard Heckman III]

Sorry for the delay here.. Could you let me know what version/build you are running? You can find this on the Help-About screen.

 

[James]

9.1.0.1256 here. Thank you!

 

[Andy McKenna]

9.1.0.1276

 

[OliverR]

9.1.0.1276 also.