Using a file location in a table

[Dropout]

Good afternoon.

I am creating a part number table and want to refer to a file location of the CAD file for the part.

What is the best Field Type to use for this?

I've attached what I have so far.

[Villeroy]

Are the cad files stored in one directory or do you need a file picker dialog to select them from arbitrary locations? In either case you need a simple macro.

[Dropout]

Hi Villeroy.

The files are in different locations so a file picker dialog would be awesome.

Where would I find or how do I make the macro?

[Villeroy]

Would you please test the code that is distributed by the attached text document? It contains Python source code and a Basic macro which installs that code to the user profile of your office suite.

If this is your first document embedded macro:
menu:Tools>Options>Security>[Macro Security...]. Set the highest security level. There you can add trusted directories from where embedded macro code is allowed to be run. Could be your personal document's folder(s) but not your downloads folder because the downloads folder gets all downloads from all sources. This setting affects the embedded install routine.
------------------------------------------------
The displayed Python code will be installed as a global macro within your office profile where all office settings are stored. This code is assumed to be trustworthy in any case.
-----------------------------------------------
Ok, download the attachment, move it to a trusted directory, open it and push the install button. You should get a success message that the document has saved as a plain text file in your user profile.
Alternative way: open first with macro warning, then save in trusted directory and finally menu:File>Reload. Then push the install button.
----------------------------------------------
Now you have 3 macros under Tools>Options>Macros>PyDBA>URLButton
1) URLButton_Approve opens the resouce that is specified by a valid URL that is stored in a database field. A valid URL starts with a protocol such as http: https: mailto: ftp: file: In your case you want to open local files specified by file: URLs. All files will be opened with their respective default program as set up on system level.
2) FileButton_Approve opens a resource that is specified by a system path like C:\Some Path\Some File.png. This macro does the same thing as 1). In the background it will convert any system path to a valid file URL. We will store file URLs.
3) FilePickerButton opens a file picker and writes the selected file into the active record's specified database column.

--------------
Create a button to store a file: URL
Draw a push button on your form and get its properties dialog.
It has a property "Additional information". There you enter the name of the database column (NOT any form control name) where the URL will be stored.
On the event tab there is an event "Execute action". Hit the ellipsis button, browse to My_Macros>pyDBA>URLButton and pick macro FilePickerButton.

Create a button to open a file: URL
Draw a push button on your form.
It has a property "Additional information". There you enter the name of the database column where the URL is stored.
On the event tab there is an event "Approve action". Hit the ellipsis button, browse to My_Macros>pyDBA>URLButton and pick macro URLButton_Approve.

Add a text box showing the URL. It is not required but it gives you a visual hint if there is an URL and which one. You should make the text box read only to prevent manual editing.

Further customization of the file picker:
Open the attached document one more time, turn on View>Non-printing characters (Ctrl+F10) and scroll down to def FilePickerButton near the end of the module.
There you find 4 lines with comments on top (starting with #), with 4 leading spaces and ŵith text in single quotes (.... marks 4 leading spaces)
....sTitle = 'URL Button File Picker' specifies the file picker's window title
....sInitPath = '' (empty pair of single quotes) specifies no initial path. It defaults to some default path of your office suite. You may put your own folder path between the single quotes, e.g. 'C:\Users\Dropout\My Files\Cad Files'. If this path exists, the file picker will start here.
....sFilterLabel = 'All Files (*)' is a descriptive text shown in the type of file list box. You may change it to 'CAD files (*.xyz)' if your files end with .xyz
....sFilter = '*' is the a filter pattern with wildcards. * stands for all files *.xyz shows only file names ending with .xyz, *.od? would show all ODF documents (starting anyhow, then .od and one more joker character), semicolon separated patterns *.txt;*.csv would show all files ending with either .txt or .csv Put your filter pattern(s) in single quotes like the other strings.
Hit the install button once more and confirm that you want to overwrite the already installed URLButton.py