Populating a File Select field automatically

[Ted Giles]

Another thing I have never got working
Situation;

Documents are saved to a specific folder.
Each document has a unique identifier - say - 123a, 123b, 123c, 124a, 124,b, 124c - the a,b,c, identify variations in the document

In my table I have records with the PK as 123, or 124 for example. They are unique.
The table has 3 fields which will containing the a,b,c
The form has a File Select field which I would prefer to be populated with the file reference automatically to avoid errors, so;

Record 123 has 123a, 123b, 123c inserted into the appropriate field
Record 124 has 124a, 124b, 124c inserted into the appropriate field

Result should look something like this;

Record;

PK = 123
a value = c:\my folder\123a.pdf
b value = c:\my folder\123b.pdf
c value = c:\my folder\123c.pdf

Question. How to insert file paths into a table based on matching key data, and also get Alpha to refresh the PDF Viewer and display the PDF once uploaded?

All help appreciated.

[Stan Mathews]

Re: Populating a File Select field automatically

You've lost me with the reference to inserting the file paths and uploading and the PDF viewer thrown in at the end.

The path to each "a" pdf is

"c:\my folder" + alltrim(str(fieldname_holding_pk_a_value))++".pdf"


the "b"'s

"c:\my folder" + alltrim(str(fieldname_holding_pk_b_value))++".pdf"

the "c"'s

"c:\my folder" + alltrim(str(fieldname_holding_pk_c_value))++".pdf"

Re-reading your post, possibly you want to examine the contents of "my folder" and populate the corresponding fields in the table for each record if a, b, and c pdf documents are found?

If so, something like this should work. (example with my table and directory)

'get pointer to destination table
tbl = table.open("atest")
tbl.order("id") 'pk field is "id"
'get a list of pdf files in the directory
lst = filefind.get("P:\executive\Mpurch\Archive\cur_mkts\*.pdf",FILE_FIND_NORMAL,"PN")
'process each path\filename
for each foo in lst
	'find the pk value in the path\filename
	str = extract_string(foo.value,"guide ",".pdf")
	' if the pk is in the destination table
	if tbl.fetch_find(str) > 0
		'write the path\filename to the where field
		tbl.change_begin()
		tbl.where = foo.value
		tbl.change_end()
	end if
next
tbl.close()

c:\my folder\123a.pdf
For ease of use it would make sense to me to use

lst = filefind.get("c:\my folder\*a.pdf",FILE_FIND_NORMAL,"PN")
lst = filefind.get("c:\my folder\*b.pdf",FILE_FIND_NORMAL,"PN")
lst = filefind.get("c:\my folder\*c.pdf",FILE_FIND_NORMAL,"PN")

and make three passes.

Your pk extraction would probably be

str = extract_string(foo.value,"r\\","a.pdf")
str = extract_string(foo.value,"r\\","b.pdf")
str = extract_string(foo.value,"r\\","c.pdf")

[Ted Giles]

Re: Populating a File Select field automatically

Thanks Stan, I had initially approached it in a different way but it didn't seem to work as I intended.
What I tried was;

Set a default value in the Rules for the field with the PDF location, because I know what that is
Example;

For the a field I captured the pk as a variable (123) then the default is c:/my folder/variable+a.pdf
for the other fields it would be c:/my folder/variable+b.pdf and c:/my folder/variable+c.pdf

My assumption was that if the c:/my folder/variable+a.pdf existed, then it would be possible to use that as the trigger for the PDF Viewer.
It appears OK in field as a default, but the Viewer doesn't fire.

I will see if I can get my head around your code, thanks for the quick response.

[Stan Mathews]

Re: Populating a File Select field automatically

Just to be clear about running three passes, I meant something like...

'get pointer to destination table
tbl = table.open("[COLOR="#FF0000"]your_table_name[/COLOR]",FILE_RW_EXCLUSIVE)
tbl.order("[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]id[/COLOR]") 'pk field is "id"
tbl.batch_begin()
'get a list of pdf files in the directory
lst = filefind.get("c:\my folder\*a.pdf",FILE_FIND_NORMAL,"PN")
'process each path\filename
for each foo in lst
	'find the pk value in the path\filename
	str = extract_string(foo.value,"r\\","a.pdf")
	' if the pk is in the destination table
	if tbl.fetch_find(str) > 0
		'write the path\filename to the where field
		tbl.change_begin()
		tbl.[COLOR="#FF0000"]pdf_a_field [/COLOR]= foo.value
		tbl.change_end()
	end if
next
lst = filefind.get("c:\my folder\*b.pdf",FILE_FIND_NORMAL,"PN")
'process each path\filename
for each foo in lst
	'find the pk value in the path\filename
	str = extract_string(foo.value,"r\\","b.pdf")
	' if the pk is in the destination table
	if tbl.fetch_find(str) > 0
		'write the path\filename to the where field
		tbl.change_begin()
		tbl.[COLOR="#FF0000"]pdf_b_field [/COLOR]= foo.value
		tbl.change_end()
	end if
next
lst = filefind.get("c:\my folder\*c.pdf",FILE_FIND_NORMAL,"PN")
'process each path\filename
for each foo in lst
	'find the pk value in the path\filename
	str = extract_string(foo.value,"r\\","c.pdf")
	' if the pk is in the destination table
	if tbl.fetch_find(str) > 0
		'write the path\filename to the where field
		tbl.change_begin()
		tbl.[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]pdf_c_field[/COLOR] = foo.value
		tbl.change_end()
	end if
next
tbl.batch_end()
tbl.close()

[Ted Giles]

Re: Populating a File Select field automatically

OK, I am learning stuff Stan, thanks for helping the hard of thinking like me.

Last soppy question (maybe).
Can I interrogate the PDF Viewer (PDFViewer1) etc to see if there is any actual data present?
Apparently there is an option for T or F but I cannot get the syntax correct in a CO.

[Stan Mathews]

Re: Populating a File Select field automatically

I'm not understanding, sorry.

If you want the pdf viewer supercontrol to display a pdf if a field contains a path to it, having already populated the field...

specify the filename in the supercontrol like

if(isnotblank("pdf_field"),alltrim(pdf_field),"")

If you put the pdf viewer on a pane of a conditional object and want to display the pdf viewer if the pdf for a field exists the condition for the pane would be alltrim(pdf_field.value) > "" or file.exists(alltrim(pdf_field)) depending onwhether you want to test the field value or the actual existence of the file.

[Ted Giles]

Re: Populating a File Select field automatically

Thanks. That's what I was after, Stan.

[Stan Mathews]

Re: Populating a File Select field automatically

It was pointed out to me, graciously, that I had an extra + in these lines, now corrected. Also corrected the backslash in the path.

The path to each "a" pdf is 

 "c:\my folder\\" + alltrim(str(fieldname_holding_pk_a_value))+".pdf"


 the "b"'s

 "c:\my folder\\" + alltrim(str(fieldname_holding_pk_b_value))+".pdf"

 the "c"'s

 "c:\my folder\\" + alltrim(str(fieldname_holding_pk_c_value))+".pdf"

[Ted Giles]

Re: Populating a File Select field automatically

Most gentlemanly!
Thanks