Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Maintain - how to get the record ID after creating a new row

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Maintain - how to get the record ID after creating a new row
 Login/Join
 
jjoyce
Gold member
posted
I'm working on a maintain project and I'm confused as to how to do this best.

The user enters the initial data in one form and then must save the results, which are written to Table1. Additional tabs then become active and the user can enter additional info on a second form that is written to Table2. The primary key from Table1 is needed before the data can be written to Table2.

When the user is running this in "edit" mode, ie they are only updating an existing entry on Table1 (possibly adding additional entries on Table2), I have a variable called RECORD_ID that is passed as a parameter from the calling fex. But I don't know how to populate that variable with the primary key value when a new record is created within maintain..

This message has been edited. Last edited by: jjoyce,


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Maintain Wizard
Master
posted Hide Post
If the RECORD_ID is a field in the database, then it's pretty easy to add a new one. For me, I like to have my segment be an SH1. This way, the RECORD_ID is the key field and the highest one is first in the database.

In my example I am assuming that the user is entering data into datastk. When they hit INCLUDE I can then use this code:

CASE INCLUDER
STACK CLEAR STK
REPOSITION RECORD_ID
NEXT RECORD_ID INTO STK
COMPUTE NEW_RECORD_ID = STK.RECORD_ID + 1;
COMPUTE DATASTK.RECORD_ID = NEW_RECORD_ID
INCLUDE RECORD_ID FROM DATASTK
ENDCASE

You can also check the value for FOCERROR and if the include fails, bump up the value of new_record_id by 1 and try again. If you don't have an SH segment then you can do this:

STACK CLEAR STK
REPOSITION RECORD_ID
FOR ALL NEXT RECORD_ID INTO STK
COMPUTE NEW_RECORD_ID = STK(STK.FOCCOUNT).RECORD_ID + 1;

Let me know if this helps.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Alan B
Virtuoso
posted Hide Post
If you use an identity column for the RECORD_ID, one approach is to insert a blank record into the first table and retrieve the identity, then update that record using maintain code. This can be done with an exec to a procedure which may look like:
SQL SQLMSS SET SERVER test_connection 
SQL SQLMSS
INSERT INTO dbo.tablename(column) 
VALUES (' ');
END
SQL SQLMSS
SELECT @@IDENTITY AS 'Identity' from dbo.tablename;
TABLE
ON TABLE HOLD AS IDOUT
END
-RUN
SET ASNAMES=ON
TABLE FILE IDOUT
WRITE MAX.Identity AS Identity
ON TABLE PCHOLD
END

using:
identStack.Identity/p32;
.
.
.
exec fexname into identStack;
.
.
.
inputStack.identityfieldname = identStack.identity;
inputStack.otherFields = value;
for 1 update table name.otherFields from inputStack;
.
.
.

The identity value can then be used for all other tables for cross_referencing.

(Also look at SCOPE_IDENTITY(), but I have not found that necessary.)


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
jjoyce
Gold member
posted Hide Post
Maintain makes my head spin Frowner

I think I understand what you described Mark, but with my existing code, I think Alan's description works better. Thats actually similar to what I wanted to do initially anyway, but I had a brain freeze and didn't realize I could just define my own stack to hold the fex output... I've only ever Inferred a stack from an existing table before. It was a bit of a derrrrr moment for me Smiler

I ended up writing a simple fex that grabs the max record_id. (Even though my column is defined as an identity, the @@IDENTITY was returning null.)

SQL SQLMSS PREPARE SQLOUT FOR
select MAX(RECORD_ID) FROM TABLE1;
END
-RUN
TABLE FILE SQLOUT
WRITE RECORD_ID
ON TABLE PCHOLD
END

In edit mode, identStack.RECORD_ID already has a value and this is skipped; if identStack.RECORD_ID = 0, then the fex is executed into identStack immediately after the Revise statement.


Thanks!!


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Maintain - how to get the record ID after creating a new row

Copyright © 1996-2020 Information Builders