Scrolling window with data from more than one seems table

(0) Share

Report

Posted on by Community Member

I am creating a lookup form and I want it to allow different fields for searching the lookup. The fields can be from different tables so using one table as the source for the scrolling window and filling the other fields in the line fill event will not work. I've tried virtual tables with mixed results. One I did worked perfectly. While the one I am currently working on comes up empty.

It is seeming more like I will need to go the route of creating a table to hold the fields I need for the scrolling window. But that means keeping that table updated.

Does anyone else have a better idea, or have you found a way to have virtual tables work reliably in GP?

*This post is locked for comments

All responses (9)

Answers (1)

Community Member on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Thanks Tim for all the information on the Temp tables. It does not look that complicated and I can see where this would come handy. We are relatively new to GP about a year into it, but from what I have already seen here I respect David's knowledge and would definitely attend a class by him if presented with the opportunity.
David Musgrave MVP ... 14,036 Most Valuable Professional on at

Like (1)

Report

RE: Scrolling window with data from more than one seems table

Tim

Thanks for the nice comments.

It is even faster to do an insert directly into the table. Reading the data set and writing one record at a time is really show in comparison when the data has more than a few records.

This blog post on my old blog has the latest conference materials from both of the cross dictionary sessions I did.

blogs.msdn.microsoft.com/.../cross-dictionary-dexterity-development

David
Community Member on at

Like (1)

Report

RE: Scrolling window with data from more than one seems table

David,

We did have training through Leslie Vail. She did a great job. Though I'd still like to got to the Summit too. Always more to learn.
Tim W 2,925 on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Hi Eric,

Here's a bit more on this. The image is the temp table I'm populating against in the above example. This is how I add it to Dexterity. Use case is that I have a table of users what are added to Doc Link (which we no longer use BTW, thank you workflow 2.0) which I want to add to a table in GP which then populates a secondary approval matrix.

I create the table, add the table to the form and then use a pass through SQL query in dexterity to query against a table in any database and read the records returned, adding them to the temp table. This table was closed/opened (just to be sure I have a refresh) on the lookup form, passed to the code below which then is used to fill the window. In it I'm reading a view created in the doclink database.

Not a lot of code to make this happen, works for me.

Side note, I took a one week Dexterity class many years ago and it's well worth your time/effort/money as this language is unique. My favorite reference material of all time is on my CD from the 1998 world wide developers conference in Fargo. It was a session (with excellent additional documentation) presented by David Musgrave on how to interact with third party products through Dexterity. David is very humble on these sites but he is a true Dex god. If you get the chance to attend his class you will be well served.

Tim

{NA_Get_DL_Users_PassThru}

inout table NAT_DL_EMail_TEMP;



local long SQL_connection, status;

local text SQL_Statements;

local boolean result;

local currency l_amount;

local string l_pre_bill_recognized;

local 'SOP Type' l_type;

{Query information}

local string l_company;

{SQL error information}

local long GPS_error_number, SQL_error_number;

local string SQL_error_string, ODBC_error_string;

{ use for current company

clear table SY_Company_MSTR;
set 'Company ID' of table SY_Company_MSTR to 'Company ID' of globals;
get table SY_Company_MSTR;
set l_company to 'Intercompany ID' of table SY_Company_MSTR;

}

l_company = "doclink2"; {this is for a directed company}

{Connect to the SQL data source.}

status = SQL_Connect(SQL_connection);

if status = 0 then

                {Build SQL statement to use the appropriate database.}

                SQL_Statements = "use " + l_company;



                {Execute the SQL statements.}

                status = SQL_Execute(SQL_connection, SQL_Statements);

                SQL_Statements = "select Name, EMailAddr from vwNATGPUsers order by Name";

                status = SQL_Execute(SQL_connection, SQL_Statements);

                if status = 0 then

                                status = SQL_FetchNext(SQL_connection);

                                while status <> 31 do

                                                clear table NAT_DL_EMail_TEMP;

                                                status = SQL_GetData(SQL_connection, 1, 'Name' of table NAT_DL_EMail_TEMP);

                                                status = SQL_GetData(SQL_connection, 2, 'String 132' of table NAT_DL_EMail_TEMP);

                                                save table NAT_DL_EMail_TEMP;



                                                status = SQL_FetchNext(SQL_connection);

                                end while;

                end if;



                status = SQL_Execute(SQL_connection, SQL_Statements);



                status = SQL_Terminate(SQL_connection);



else

                error "An error occurred executing SQL statements.";

                                                                {Retrieve the specific error information.}

                status = SQL_GetError(SQL_connection, GPS_error_number, SQL_error_number, SQL_error_string, ODBC_error_string);

                if status = 0 then

                                warning "GPS Error: " + str(GPS_error_number);

                                warning "SQL Error: " + str(SQL_error_number) + " " + SQL_error_string;

                                warning "ODBC Error: " + ODBC_error_string;

                else

                                error "Unable to retrieve SQL error information.";

                end if;

                {Disconnect from the SQL data source.}

                status = SQL_Terminate(SQL_connection);

end if;
David Musgrave MVP ... 14,036 Most Valuable Professional on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Hi Erick

Have you done the Dexterity training?

If not, I would suggest coming to GPUG Summit, I will be running training in the week before the conference.

David
Verified answer

Community Member on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Hi David,

I started off with Virtual Tables, but had the issue described were no data was showing. In looking things over again I saw that there was a table created with the same physical name as the virtual table. Turned out that was causing my issue so I renamed the virtual table and then it worked. I did have to perform a create table from the SQL Maintenance screen though which I thought was not necessary for Virtual tables. I am glad I found out here about Temp Tables though. I think I might play around with them and use them when they fit the situation.

Thanks!
Suggested answer

David Musgrave MVP ... 14,036 Most Valuable Professional on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Hi Erick

You can use Virtual tables which are implemented as views in SQL, or use a temporary table.

You can create the temporary table (Physical name TEMP) with all the fields you are interested in and then use pass through SQL to populate it quickly using an insert based on a select statement.

You will need to use Table_OSName() to get the path for the temp table to pass through to the SQL_Execute() statement.

David
Community Member on at

Like (0)

Report

RE: Scrolling window with data from more than one seems table

Tim, thanks so much for this reply. I have not heard of temp tables yet but this sounds like it might be what I need. When I mentioned the window comes up empty, there was no stored procedure involved. I was using a virtual table as the source table for the window , but using fill window for this window always returned empty.

In the database a view is created for that virtual table. Querying directly on the view I could see data. So that was the source of my confusion.

I'm going to try out this temp table method and see how it works for me. Thanks!
Tim W 2,925 on at

Like (1)

Report

RE: Scrolling window with data from more than one seems table

I assume you are creating a table with TEMP as physical name when you reference virtual tables.

I use temp tables as often as I can in all my processes as they are just pretty darn easy to work with mostly. I don't use c-tree tables ever (even though many will say they are faster) but constantly use tempdb sql temp tables to handle situations like you are describing. I can easily add or remove fields when testing or doing further changes for production and not have to manage an in place conversion of an existing table. Love this feature in Dexterity.

When you say the one you are working with comes up empty sounds like you are using a procedure to fill the temp table but not opening it from a script on the form then passing it to the procedure but rather opening it in the procedure itself.

The one thing to do on these is to open the table where you need to use it (your lookup form) and then pass it as an inout parameter to any procedures you are using to fill or manipulate it. Once you get that top of mind I would think the empty issue goes away.

Here's a code sample from a temp table I use to populate a lookup form. I run it from a script I call once the user opens the lookup window.

Open form...

If open(form....)

Run Script '(L) Fill Scroll';

end if;

{Script '(L) Fill Scroll'}

close table NAT_DL_EMail_TEMP;

open table NAT_DL_EMail_TEMP;

call NA_Get_Users_PassThru, table NAT_DL_EMail_TEMP;

fill window CC_Scroll table NAT_DL_EMail_Setup;

focus 'Name' of window CC_Scroll;

I then use the NA_Get_Users_PassThru procedure to populate the table.