I am having trouble figuring out what is causing this, but I have a custom form that is populated from a custom dexterity table. If I query the table I can see that the data in the table is populated for all fields. Yet If I look at the table buffer from my scrolling window in the form one field, the item number appears blank.
You can see the ItemNmbr from SQL above but it is missing in the buffer below
*This post is locked for comments
Thank you all for all this information It really has helped me understand how the different ways of defining the range work and interact with Sql Server. We have used virtual keys before but having all this information together on one post helps me understand how all this works better.
Point taken and you are 100% correct. I jumbled my comment with something else I was thinking (at past midnight my time). I meant to say, the assign key statement is typically used when retrieving data from a SQL table, but there isn't a key available that will sort the data in the desired order.
Using either virtual keys or where clauses will not use the zDP sprocs and so there will be performance hit. The hit is nothing like the 300% difference when the sprocs were first introduced for SQL Server 6.0. But it will be slower.
Mariano,
Virtual keys do allow sorting in ascending and descending order, I have used it so many times.
Share
Report
All responses (
Answers (
assign virtual_key2 as key for table ES_Item_Detail_Temp with
KEY_OPTION_ALLOW_DUPLICATES using 'User ID' of table ES_Item_Detail_Temp,
Condition_Name of table ES_Item_Detail_Temp with KEY_SEGMENT_OPTION_DESCENDING;
range clear table ES_Item_Detail_Temp;
range table ES_Item_Detail_Temp where physicalname('User ID' of table ES_Item_Detail_Temp)+"='"+'User ID' of globals+"'";
fill window ES_View_Item_Detail_Scroll of form ES_InventoryItemList table ES_Item_Detail_Temp by number virtual_key2;
Your script above should read something like this:
range clear table RMCCS_ProfileItems; range table RMCCS_ProfileItems where physicalname(RMC_CustomerProfileID of table RMCCS_ProfileItems) + CH_SPACE + CH_EQUAL + CH_SPACE + SQL_FormatStrings(string(RMC_CustomerProfileID)); fill window rmccsProfileItems;
I am also under the assumption that you RMC_CustomerProfileID field is a non-string value (?), i.e., integer. Otherwise, there's no need to cast it to string, hence that line would read something like:
range table RMCCS_ProfileItems where physicalname(RMC_CustomerProfileID of table RMCCS_ProfileItems) + CH_SPACE + CH_EQUAL + CH_SPACE +SQL_FormatStrings(RMC_CustomerProfileID);
I love my friend David Musgrave to death, but his answer above is not entirely accurate. When using a range where statement, the burden is placed on the SQL Server Query Optimizer to use the index that will give the best performance, before it (the Optimizer) resorts to a table scan. This approach isn't any faster or slower than using an actual indexed range and maybe necessary (and preferable) when you don't have an index that totally satisfies your range criteria.
Dexterity also allows you to set a virtual key on a table using the assign as key statement. The only thing to consider here is that assign will not sort the data in the desired order because there isn't an actual key. Again, the SQL Server Query Optimizer plays a major role here as it needs to determine the best index out of the available ones that best matches the "virtual" indexed range.
Stored Procedures refer to SQL procedures which can be called with the call sproc command.
I think you are referring to Dexterity Procedures and Functions which can be global or form level.
It is very common to have subtly different code to display the description for the change and fill/display events.
What you can do is populate the Item Number on the window and then use run script on the Item Description field from both your change and fill scripts. Then place the script to retrieve and display the description in the change script of the Item Description field.
That is similar to the procedure approach, because it re-uses code. But it does use parameters which can provide a layer of abstraction.
As long as it works.
David
PS: If you attend Dexterity training we take your through some examples which will help you troubleshoot and understand issues like this. I am running training in the week before GPUG Summit.
winthropdc.wordpress.com/.../announcing-dexterity-training-at-gpug-summit-2017-in-nashville-tn-usa
Ok I figured out the mystery.
On the form the Item Number is brought in from the profile items table shown above. This is populated into a combo box which the user can use to select a different item when they are working on the form. There is an item description which come from the GP Item Master table using the item number in the combo box.
To reuse the logic we decided to call the item number change script after our fill of the item number, thinking it would save us from copying the logic to get the description in the fill event script for the window.
One thing the change script does is right back the item number to the profile items table to save changes the user makes.
Apparently the item number combo box is not recorded until the fill event trigger finishes, because when the change script is executed from the fill script, the field is empty in the change script and it copies back the empty field to the profile items table which is why I saw what I saw.
What I have to do then is copy the logic I need from the change script into the fill script. I know a stored procedure would be a way of reusing the code, but the logic is not complex enough for a stored procedure, I don't think.
Being new to GP I'm not sure what the common practice would be. Is it more the case that redundant code is always placed in stored procedures or does it depend more on the complexity of the code?
David,
I copied your logic and it gave me the same results. This time I did it with no breakpoint, but checking the table buffer after the fill. I moved up the records to the first one with data and updated the field sin the Table Buffer window to show me what was in there. I got the same results as pictured above and the data when viewed through SQL shows the same content as above as well.
The break point was after on the fill event of the window so that it stopped after the first record and I could see the contents. Thanks for these other tips.
#1
Almas Mahfooz
3
User Group Leader
