Discussions

Expand all | Collapse all

File Attachment field as Lookup Field

Xavier Fan05-13-2015 21:29

Xavier Fan08-27-2015 20:26

  • 1.  File Attachment field as Lookup Field

    Posted 05-02-2014 12:05

    Does anyone have any suggestions for simple ways to either display or link to a File Attachment on a parent record within the child record?

    We have a parent table of procedures, which has a file attachment field of documentation. We have a child table of issues, where the resolution could be to select one of our common procedures to follow from a drop down list of all procedure records.

    I would like to do this so the file attachment can be accessed directly from the child record without increasing the number of clicks from linking to procedures then into the file attachment or making a many to many relationship between the table and having to have extra steps to set up the links in the first place.



  • 2.  RE: File Attachment field as Lookup Field

    Posted 05-02-2014 12:45
    Can two separate child records (Issues) be pointing to the same parent record (Procedures)?  Or in other words - can Procedure A be the resolution for both Issue B and Issue C?  (EDIT:  a 1-to-many relationship should be sufficient here:  One Procedure has many Issues).

    If one Issue can have multiple Procedures, you'll need a many-to-many relationship - between Procedures and Issues.   (EDIT:  to clarify when you'd need a many-to-many relationship).

    There may well be some tricky and clever ways to set this up - but I think you'd be going to a bunch of effort to avoid the most straightforward way - which is a many-to-many relationship.


  • 3.  RE: File Attachment field as Lookup Field

    Posted 05-02-2014 12:49
    Yes, we could have multiple children pointing to the same parent. I had considered a many to many but that adds additional steps in the establishment of the relationship and there might as well be an extra step in finding the document
    I was thinking if there was some way to get the URL of a specific field I could pass that down as a lookup field and use that.


  • 4.  RE: File Attachment field as Lookup Field

    Posted 05-02-2014 14:19
    I've created an example app in Exchange - called "Example - Lookup File Attachment through Many-to-Many Relationship". You can find it here:


    https://www.quickbase.com/db/main?a=AppDetail&ID=1439&app_name=Example%20-%20Lookup%20File


    There are 3 tables:
    - Procedures - with a File Attachment field
    - Issues
    - Resolutions - the intermediate table in the many-to-many relationship between Procedures and Issues.

    The basic set up for a Many-to-Many relationship is detailed here:
    http://www.quickbase.com/user-assistance/Default.html#many_to_many_relationship.html

    You cannot lookup a File Attachment field directly.
    To enable a lookup to the File Attachment field (in the Procedures table) - create a Formula Text field (called "File Attachment Link" here, with HTML tags enabled) with this formula:

    If ([File Attachment] = "", "",
    "<a href='" & URLRoot() & "up/" & Dbid() & "/a/r" & [Record ID#] & "/e7/v0' target='_blank' >" & [File Attachment] & "</a>")

    Note the "e7" portion of the formula. The "7" refers to the Field ID of the File Attachment field. Modify accordingly for your own app.

    This "File Attachment Link" link field can now be looked up in the Resolution table, and also on the Issues table.

    In the example app - see that Procedure A is the Resolution for Issues 1 and 2 - with the same Document A accessible from both. Procedure B is the Resolution for Issue 3. Issue 4 is resolved by both Procedure A and Procedure B.

    The only real additional step in setting the relationship (on top of a normal many-to-many relationship) - is setting the File Attachment Link field. Once you have that - you can look it up normally.

    So once you're in the Issue, you press "Add Resolution", and select the Procedure. Then on the Issue, you can now see the File Attachment Link for that Procedure, which you can click directly to download the document.

    Hope this helps!


  • 5.  RE: File Attachment field as Lookup Field

    Posted 05-02-2014 14:48
    Thank you very much, your bit of code has enabled me to do exactly what I needed to do without all the extra steps from using Many to Many relationships!

    The Formula Text field can be brought through as a lookup field into the child table.


  • 6.  RE: File Attachment field as Lookup Field

    Posted 05-02-2014 14:57
    Ok great!  If all you need is a 1-to-many relationship (One Procedure has many Issues) - then that should be sufficient.


  • 7.  RE: File Attachment field as Lookup Field

    Posted 05-12-2015 21:26
    Hi Xavier Fan,

    I was able to use this in my application but when i try to click on the file attachment link I get the following error: the specified field was not a File Attachment field. Please help.


  • 8.  RE: File Attachment field as Lookup Field

    Posted 05-13-2015 14:19
    Did you use the Example app, or did you implement it on your own app?

    In the formula for the link (which should be in the same table as where the File Attachment is located):

    If ([File Attachment] = "", "",
    "<a href='" & URLRoot() & "up/" & Dbid() & "/a/r" & [Record ID#] & "/e7/v0' target='_blank' >" & [File Attachment] & "</a>")

    Note the "e7" portion of the formula.  The "7" refers to the Field ID of the File Attachment field.  Modify accordingly for your own app.

    I suspect that the field with fid = 7 - is not the File Attachment field in your app.  Replace the "e7" with "e[fid]" - where [fid] is the fid for your File Attachment field.


  • 9.  RE: File Attachment field as Lookup Field

    Posted 05-13-2015 21:00
    Hi Xavier fan,

    Field ID was the issue I have two similar fields and I got the wrong field ID in their. Working now. Thank you so much for the quick response.


  • 10.  RE: File Attachment field as Lookup Field

    Posted 05-13-2015 21:29
    Great!


  • 11.  RE: File Attachment field as Lookup Field

    Posted 08-27-2015 19:22
    Worked perfectly for a 1 to many relationship! Thank you!!!


  • 12.  RE: File Attachment field as Lookup Field

    Posted 08-27-2015 20:26
    Good to hear!


  • 13.  RE: File Attachment field as Lookup Field

    Posted 10-28-2018 19:27
    I am trying to do this exact thing.  The problem I seem to be having is that for some reason my actual record ID # and the file attachment URL ID don't match.  Where you have e7 matching record 7, I have "rb" matching to record 1.  And "rc" matching record 2.  For some reason the record ID does not match my file attachment URL, so if I insert [Record ID #] I get an error.  If I remove this and insert the "rb" or the "rc" etc., then it works.  But this doesn't work overall because I need to associate each record with each file attachment.  How can I do this?  Is there a formula to use [Record ID] to change to the appropriate letter?  



  • 14.  RE: File Attachment field as Lookup Field

    Posted 10-28-2018 19:31
    Let�s start at the beginning. Make the formula field that Xavier suggested. Make sure it works on the same table where the file attachment is. Then use that field as a lookout.

    If you can�t get your formula to work I need help with that then please post your formula.


  • 15.  RE: File Attachment field as Lookup Field

    Posted 10-29-2018 02:33
    this is what I have entered into my formula for the url-formula field:

    If ([Attached Form] = "", "",
    "<a href='" & URLRoot() & "up/" & Dbid() & "/a/r" & [Record ID#] & "/e8/v0' target='_blank' >" & [Attached Form] & "</a>")

    [Attached Form] is the document link in the record.  and the Field ID for that is 8.  When I click the link I get the following error:

    This site can�t be reached

    %3Ca%20href%3D%27https�s server IP address could not be found.

    It seems like a simple syntax error I am missing?  




  • 16.  RE: File Attachment field as Lookup Field

    Posted 10-29-2018 09:06
    I've just checked your syntax against mine which is working, and the only difference is that I'm using /e9/v0 rather than /e8/v0.


  • 17.  RE: File Attachment field as Lookup Field

    Posted 10-29-2018 02:36
    FYI: I don't see an "HTML tags enabled" box when i create the Text-Formula or the URL-Formula 


  • 18.  RE: File Attachment field as Lookup Field

    Posted 10-29-2018 11:40
    The field type for your field needs to be formula rich text. Your formula field is likely formula URL