link to file based of related field in parent table

Hi Matt,

You can link to the file attachment uploaded to the most recently-entered child Document record, on the Project record, by using a Formula - URL field. That formula field will need a couple helper fields on the relationship to make sure the link only displays on the project when the most recently-entered document record actually has an attached file uploaded. Here's how:

1. Create a summary field on the Projects table, called Maximum Document Record ID#. It should take the maximum value of the documents' Record ID#s.
2. Create a lookup field on the Documents table called Project - Maximum Document Record ID#, pulling down the new summary field above.
3. Create a Formula - Checkbox field on the Documents table called File uploaded?, to check whether your file attachment field has a file uploaded to it. The formula should be: If(Length([File]) > 0, true, false)
4. Create one more summary field on the Projects table, called # of Files in Most Recent Document. It should count the number of related documents. Under Additional Criteria, add two lines of filters that look like this:
-- (File uploaded?) (is equal to) (checked)
-- (Project - Maximum Document Record ID#) (is equal to) (the value in the field) (Record ID#)
5. Now it's time to make the Formula - URL field! You can start with this formula: If([# of Files in Most Recent Document] = 1, URLRoot() & "up/" & [_DBID_DOCUMENTS] & "/a/r" & [Maximum Document Record ID#] & "/e6/v0")

There are two pieces you'll need to replace for your formula:

- You'll need to replace DBID_DOCUMENTS with the table alias for your Documents table (you can find that by going to table settings, hitting advanced settings, then scrolling to the bottom of the page)

- You'll need to replace the number in the e6 portion with the field ID of your file attachment field. So, if your file attachment field has field ID 20, then you would use e20 here.

------------------------------
Brian Cafferelli
------------------------------