Insert a form record without leaving the form

[jtdunn]

What is the correct embed to insert a form record without leaving the form (without using the form save button)?  Here is the scenario.  I am accessing data on an MSSQL 2005 server and the identity values for all records are generated on the back end.  When I insert a form (parent) the user has the opportunity to add child records.  In order to do that, I need to insert the parent record and retrieve the identity value for the child records when the user presses the insert key to go to the child form to add the child record.

John

[Bruce]

Hi John,

Ok, so really what you need to do it this;

a) If the form is in insert mode don't have the child browse
b) If the form is in Insert mode, then the "Save" button should go to the form, but now in Change mode
c) If the form is in Change mode then display the child browse.

That's all possible to do - but seems a little long-winded.

The default behavior for a form like this is the "clarion way". In other words on Insert the record is created in the database. Thus the user is free to add child records at the same time as "Inserting" the parent record, and it all just works.

Before we get to to the details of how you do the 3 steps mentioned earlier, could you perhaps suggest why that is better than the current default behavior?

I suppose what I'm saying is that I'm keen to improve it if you think there is a flaw in the default behavior.

Cheers
Bruce

[jtdunn]

Hi Bruce,

Thank you for your reply.  For now, to get around my 'problem', I am hiding the insert button in the Order form when inserting a new order.  The user has to save the order, then re-enter the order form to be able to add line items.

I defnitely do not want to follow the 'Clarion way' of inserting an empty record in the database.  I have stopped doing this in my Desktop applications using MSSQL for years.  In a multi-user app, this inserted record will show up in other users browses unless care is taken to filter them out.  Plus with MSSQL it is not always easy to add an 'empty' records when certain fields are required (and the values are not know at insertion time) by the database.  My preference is not to do it the 'Clarion way' but to insert the record either by saving the form or before inserting the first child record.  I like your option B - if the form is in Insert mode, then 'Save' button should insert the new header record and go to the same form now in change mode.  That should work (or automatically inserting the header/form record, after validation, when pressing the insert button and then going to the line item form if the header/form record was successfully inserted).

John

[Bruce]

Hi John,

>> Plus with MSSQL it is not always easy to add an 'empty' records when certain fields are required

The solution to this bit (in case anyone else is reading) is simply to prime the record with "valid" values. For example, if a "Date" field "cannot be zero" then prime it with a date (for example today.)

Back to you John, you can set the URL for the save button in code, making it different for insert & change. You can get the details for what needs to be in the URL by checking out FAQ number 4. Examine the source of the procedure and you should be able to easily set the URL, and test the condition appropriately.

However it would be nice if the template did all this for you, so I've added that to the list. You may prefer to stick to your current work-around until that's complete.

cheers
Bruce

[jtdunn]

Hi Bruce,

Thanks again.  Yes, you are correct about the MSSQL insert ... I could prime required fields with dummy values (in some cases I don't know the real value until the user enters the required value) ... but it can get messy. 

Thanks for pointing me to example 4.  I'll look there for the needed code for now and thanks for considering this for the template.

BTW, I sent in a support issue where in a form that contains a browse I am finding that the many times after adding a child record the last line (most recently added record) is not being displayed.  In order to display it I have to close the form and then open it again.  Do you have any ideas about why this might be happening (and what I can do about it)?

John

[jtdunn]

Hi Bruce,

I tried setting the URL on the Save button by adding this to the 'URL on Save' entry:

 'OrderForm?<!-- Net:s:SID-->&Change_btn=Change&'

this seems to require my entering this on the 'URL on Cancel' entry:

p_web.getsessionvalue('SaveReferOrderForm')

because the Cancel was exhibiting the same behavior as the Save button when I left the entry blank.

The 'URL on save' produces a STOP that is not in my code:

Record not Found: 5 dbo.OrdHeader [] 30 Entry Not FoundOrdHeader:PK_OrdHeaderID.

Of course, at the point the redirection to the URL happens it most likely does not know the OrdHeaderID.  I tried to get that ID in the PostInsert embed (and I can get it there) but it is too late.  The above error has already happened.  Regardless, there does not seem to be a way to make the URL on Save conditional.  That is, I only want to return to the same form, in change mode, if the original action was an insert action.  When I have the 'URL on Save' set it tries to reload the form when the current action is changerecord.

I've spent about two and half hours trying to get this to work.  It most likely is possible but I don't know enough about how things are working internally to make it happen. 

I decided to make the child browse on the order form a file loaded browse and that seems to have fixed the problem with displaying the just entered child record.

John

[Bruce]

Hi John,

Yes, the issue is how to set the record ID as part of the parameter list, when we don't know the number at the time the URL is generated.

Fortunately there is a way. The command line is essentially going to get read into the parameter queue (ie the _Value_ queue - not the SessionValue Queue). And there is a way to add values to this queue _as long as you are on the same thread_. Let me say that again. _As long as you are on the same thread_.

As it happens, the PostInsert embed of the "Insert Form" is on the same thread as the "Init" part of the Change Form. So you can add the ID as a _Value_ (not sessionvalue) in the PostInsert embed.

The easiest way to make the URL conditional is to simply use a local variable there. Then right-click the procedure, and search for the variable (ie where it is being used.) _Before_ it is used you need to set the contents of the variable appropriately.

Regarding the child browse, and the additional line items. Since it is a "short list" I'd probably recommend a file-loaded browse here anyway. However it should return to the correct line. However we should deal with this problem on a separate thread if you want to take it further.

Cheers
Bruce

[jtdunn]

Hi Bruce,

Thanks again for getting back to me and tips for getting the record ID into the parameter list.  It would be very useful if the documentation spoke to this and the meaning of threads in NetTalk web apps more.  (Perhaps it does and I have forgotten, so I'll be reviewing the docs again).  I'll also try the make the URL conditional using a local variable.

So far the page loaded (short list) browse of child records appears to be working correctly.  If  I run into the problem in page loaded browses again I'll send another support message.

John

[jtdunn]

Hi Again Bruce,

I just wanted to say that your two suggestions work perfectly!  Thanks again for pointing me in the right direction!

John