opening forms when child data doesn't exist

[davidv43]

I have a master table which is associated with a form. On the form, there are buttons which should open another form as a dialog. On that form, if there are child records, it should display them with the appropriate navigation supercontrol on the form.

This works fine using action scripting to open the form and setting the filter for records for the dialog form to the common key field between the master and child tables. However, when I try to push the button and no child records exist, i get an error message saying that "No records satify the query".

What should happen is that if no child record is there, then a form should open to allow entry of a new record.

Is this possible?

Dave Volgas

[Tom Cone Jr]

Re: opening forms when child data doesn't exist

Yes, though it may require a bit of xbasic. The sequence would be something like this:

a) determine if a child record already exists. There are lots of ways to do this. I prefer key_exist().
b) if there is no child, then load the dialog form, hidden, begin the new record, pass the primary key to the new record as its foreign key, then show the form.
c) force user to click button in the dialog form to save the new record. Use that button's script to restrict further enters, or modify navigation restricitons, if desired.
d) if there is a child then use your action script

-- tom

[Nick Oldfield]

Re: opening forms when child data doesn't exist

I have a master table which is associated with a form. On the form, there are buttons which should open another form as a dialog. On that form, if there are child records, it should display them with the appropriate navigation supercontrol on the form.

This works fine using action scripting to open the form and setting the filter for records for the dialog form to the common key field between the master and child tables. However, when I try to push the button and no child records exist, i get an error message saying that "No records satify the query".

What should happen is that if no child record is there, then a form should open to allow entry of a new record.

Is this possible?

Dave Volgas

You can do this by having the form display some fields from the parent record and then have the child records in an embedded browse. Look at the way that Alphasports does it.
I use a similar technique. I have a list of invoices. If you double-click on an invoice number, a form opens displaying the invoice header, plus any items already associated with the invoice but in an embedded browse. Zero child records is fine. When you move focus to the embedded browse, you can add a new invoice item to an existing invoice. .

[dik_coleman]

Re: opening forms when child data doesn't exist

Welcome to A5....
I hate when that particular error happens, and it's really a pain.

First and foremost; learn how to write and use functions. Then use functions
to run as much as is possible. You will hear that scripts do not stop execution
when a side process or calculation is necessary. This is true but a function will
complete itself before allowing any other action.

So why am I telling you all this?????

Try these functions to call forms/modal forms/menus instead of using the
'script_play.....' commands.

Using the 'form view' function will allow the entry of a new record into the table
that is empty.

One word of caution -- you might be careful how you put a new record into a
child table. Make sure that the parent record is properly referenced....

Have fun
D

'Date Created: 24-Jun-2007 04:53:32 AM
'Last Updated: 02-Nov-2007 03:56:10 PM
'Created By  : R. Coleman
'Updated By  : R. Coleman

'just supply the name of the menu to the function
'CallMenus("mnumainsys")

FUNCTION CallMenus AS C (mnu_name AS C )

	DIM Shared mnuptr as P
	DIM mnu_name as c
	
	mnuptr = :Form.view(mnu_name)
	
END FUNCTION

Date Created: 02-Nov-2007 05:06:20 PM
'Last Updated: 20-Mar-2008 02:37:33 PM
'Created By  : R. Coleman
'Updated By  : R. Coleman
'
FUNCTION CallFrmModal AS C (tbl_name AS C, form_name AS C, form_filter AS C, form_order AS C )
	
	'just supply table name, form name, form filter and form order to the function call
	'EXAMPLE: to call the form (fr_940form) based on the table (p_wh940tax) for all records (.t.) in record number order (recno() )
	
	'CallFrmModal("p_wh940tax","fr_940form",".t.","recno()")
	
	'for forms that do not require a 'form.view_queried' access, just use the 'CallMenus' function......
		
	'start of script
	'code here
	
	dim ptx as P
	dim numrec as N
	
	ptx= table.open(tbl_name,FILE_RO_SHARED)
	numrec=ptx.records_get()
	ptx.close()
	
		if numrec>0 then		'
			form_filter = ".t."
			form_order  = "recno()"
			varP_form_name = :Form.viewqueried(form_name,form_filter, form_order ,"dialog","Center","Center")
		else		
			varP_form_name = :Form.view(form_name,"dialog","","Center","Center")
		end if
	
	'After the user closes the dialog form, the form remains in memory and the script 					
	'continues with the next command.																	
																										
	'You can insert any code here that you want to execute before the dialog form (which is now			
	'hidden) is removed from memory with the .close() command. 											
	'The code that you insert here can refer to the form using the pointer variable, varP_form_name	
	
	if is_object(varP_form_name) = .t. then 																									
		varP_form_name.close()
	end if
	
	the_end:
	on error goto 0
	
	end	
		
END FUNCTION

Date Created: 02-Nov-2007 04:56:43 PM
'Last Updated: 15-Aug-2008 01:33:25 AM
'Created By  : R. Coleman
'Updated By  : R. Coleman
'
'just supply table name, form name, form filter and form order to the function call
'EXAMPLE: to call the form (fr_940form) based on the table (p_wh940tax) for all records (.t.) in record number order (recno() )

'CallFrmView("p_wh940tax","fr_940form",".t.","recno()")

'for forms that do not require a 'form.view_queried' access, just use the 'CallMenus' function......

FUNCTION CallFrmView AS C (tbl_name AS C, form_name AS C, form_filter AS C, form_order AS C )

	ptx= table.open(tbl_name,FILE_RO_SHARED)
	numrec=ptx.records_get()
	ptx.close()
	
	if numrec>0 then		
		form_filter = ".t."
		form_order  = "recno()"
		varP_form_name = :Form.viewqueried(form_name,form_filter, form_order ,"","","")
	else
		varP_form_name = :Form.view(form_name,"","","","") 
		ui_msg_box("Table Is Empty","You may now ENTER a new record or just CLOSE this form")
	end if
	
END FUNCTION

[davidv43]

Re: opening forms when child data doesn't exist

thank you to each person who answered this query.

Tom: the key_exist function only works with the primary table, so I can't use it here. I used Tablecount function instead.

Nick: my child form has far too many fields to fit in a browse, but it is a useful technique when there aren't many fields.

Dick: I understand your code and have modified it as below. The problem is now, that even though the program logic sees there is no child record and executes the form.view function, the form is populated with an unrelated child record when it opens. I don't see what's wrong with this code.

Thanks again.
Dave

[davidv43]

Re: opening forms when child data doesn't exist

whoops, here's the code:

dim numrec as N
filter = "MRN = '" + trim(selMRN) + "'"
numrec = tablecount("scores",filter)

if numrec>0 then '
varP_form_name = :Form.viewqueried("frmScores",filter,"" ,"dialog","Center","Center",.t.)

else
varP_form_name = :Form.view("frmScores","dialog","","Center","Center")
:frmScores:mrn.value = :frmOverview:mrn0.value

end if


if is_object(varP_form_name) = .t. then
varP_form_name.close()
end if

[davidv43]

Re: opening forms when child data doesn't exist

specifically, it form.view opens the form with the last query ...

[davidv43]

Re: opening forms when child data doesn't exist

Don't know why the form,view didn't work, but this does:

dim frm as P
dim numrec as N

filter = "MRN = '" + trim(selMRN) + "'"

numrec = tablecount("scores",filter)

if numrec>0 then '

frm = :Form.viewqueried("frmScores",filter,"" ,"dialog","Center","Center",.t.)

else
frm = Form.load("frmScores","dialog","","Center","Center")
frm.New_Record()
frm:mrn.value = :frmOverview:mrn0.value
frm.show()

end if


if is_object(frm) = .t. then
frm.close()
end if

[davidv43]

Re: opening forms when child data doesn't exist

One more thing to make this the cat's meow ... how do I set the initial focus on the form to a different field?

[Al Buchholz]

Re: opening forms when child data doesn't exist

One more thing to make this the cat's meow ... how do I set the initial focus on the form to a different field?

Change the tab sequence?

or use the activate object command in a script...

[davidv43]

Re: opening forms when child data doesn't exist

you're right... frm:<object>.activate() works

[Tom Cone Jr]

Re: opening forms when child data doesn't exist

David, you're right about Key_Exist(). My mistake. I was thinking of the Exist() function, which works well with a table that is not included in the current form. Key_Exist() seems designed to work with tables that are open in the current form, so it wasn't the right choice for your situation. Sorry. -- tom

[dik_coleman]

Re: opening forms when child data doesn't exist

I use the form view only as a means of avoiding the 'no records' error when
viewing the form. Since you want ONLY parents w/children, the parent table
already exists (with records) and so does the child table. I think that the
form view function will populate with the last query or maybe with the first
record in the child table. Glad you were able to use the functions.
D