Discussions

 View Only
Expand all | Collapse all

Formula Rich Text Button - File to Add Record

  • 1.  Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 17:12
    Hi, 

    I'm using a formula rich text button to add record in a different table when click. The button includes the action of copying multiple field values into the newly added record. I have one field that is a file upload field and need to copy this file into the corresponding field on the newly added record as well. Can this be done in the rich text formula? If not, how might you do this?

    Example:
    Approval Record:
    Name 
    Date
    Amount
    JPG (image of the agreed terms)

    When clicking 'Approve' button, a new Transaction Record is created with all of the approved info:
    Name
    Date
    Amount
    JPG (***Need to duplicate this file to the newly created record)

    Thank You!
    Chris

    ------------------------------
    Chris
    ------------------------------


  • 2.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 18:48
    It is not possible to use a formula URL to copy a file.  That would require a code page, I believe.  however, if its useful you van easily provide a link to a file (as it seems that each record swill need the same file).  Then the user would easily pull down own a copy of that template file.

    You could have a documents table to store such standard templates.  If you tell me the DBID table name and the field in which the file attachment is stored and the Record ID of the record that has the std file, I can give you the formula to link to it.

    The link will look like this

    URLRoot() & "up/" & [_DBID of where the file is stored] & "/a/r" & [Record ID#of where the file is stored] & "/e99/v0"


    Replace the e99 with exx where xx is the field ID where the file is stored.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 19:16
    Thank You, Mark! 

    To give you a bit more context, these files are always unique - almost always a screenshot or pdf of an email with an outside person confirming the current property/broker commission rate. 

    That being said, if there's a way to populate the link for the file and push that link to the new file that would work! Am I dreaming there?
    The trouble is these two records (separate tables) are not related in any way currently. It's meant to be an electronic carbon copy if you will.

    Anything come to mind?



    ------------------------------
    Chris
    ------------------------------



  • 4.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 19:24
    Well it seems to me that the record you are launching off of has the file attachment so you can easily build  a link to the very record that you launched off of and the field that has the file attachment in it.
    If you want to file attachment to appear exactly as it does on the original record then let me know and I can help you if you need it with the format for a hyperlink field which would have the file name for the user to click on as opposed to a "click here" type button


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 19:30
    Having the file attachment appear exactly as it does on the original record sounds perfect!

    So does that mean this would be a local formula field that displays the associated link and then that field would be copied into the new record?

    If that's accurate and you can give me the basic formula breakdown, I'd really appreciate it! I've been thinking through this one for too long with no success.


    ------------------------------
    Chris
    ------------------------------



  • 6.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-15-2020 22:22
    OK, so you want a hyperlink to open a file.  Create a Rich Text formula field on the Source record 

    var text Words = ToText([File Attachment field]);

    // replace the 99 below with the field of the field holding the file attachment.
    var text URL =URLRoot() & "up/" & dbid() & "/a/r" & [Record ID#] & "/e99/v0";

    // this last part is always the same
    "<a href=" & $URL & ">" & $Words & "</a>"

    If the nature of the documents are that they will open up in the browser, you probably want to force a new tab, so replace the last line with this

    <a target='_blank' & $URL & ">" & $Words & "</a>"

    Then copy the value of the formula field to the target record into a Rich Text field type.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Formula Rich Text Button - File to Add Record

    Posted 06-30-2020 23:26
    Thank You, Mark!

    I ran into an issue in the formula so I switched it around to this:

    var text Words = ToText([Deal Terms Documentation]);

    var text URL =URLRoot() & "up/" & Dbid() & "/a/r" & [Record ID#] & "/e268/v0";

    "<a href=" & $URL & " target=_blank>" & $Words & "</a>"

    The issue I'm running into now is when running the Rich Text Formula button to copy I'm getting an issue during the copy. Do I need to format the field in the copy formula?
    & "&_fid_300=" & [Deal Terms Documentation Link]

    This XML file does not appear to have any style information associated with it. The document tree is shown below.
    <qdbapi>
    <action>API_AddRecord</action>
    <errcode>0</errcode>
    <errtext>No error</errtext>
    <rid>49</rid>
    <update_id>1593573745472</update_id>
    </qdbapi>

    I did also find another one of your posts on doing similar using Formula URL to URL field, but I figured I'd check if there's a way to make the Rich Text option work because it displays better with the file name.

    Thank You!
    Chris


    ------------------------------
    Chris
    ------------------------------



  • 8.  RE: Formula Rich Text Button - File to Add Record

    Posted 07-01-2020 07:29
    I am confused by your question. This thread is all about how to display a file. Then suddenly you were asking about an error when copying or creating a record. So now I'm confused what you were asking about

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Formula Rich Text Button - File to Add Record

    Posted 07-01-2020 11:02
    The copying part is back to the initial use case, but I might be being inconsistent with my jargon. 

    The goal is to create the link field that then can be included in a rich text button that when clicked adds a record to another table - copying a number of fields including this link field. In the new record, the then copied link should be able to be clicked to download or open the document stored in the original record.

    I realize that's wordy - 

    Breakdown
    - Original Record contains a file upload field where a doc is uploaded/kept. 
    - Once uploaded the user clicks the Approve button on the original record (Rich Text Formula button on the Form)
    - New record is then created in the destination table with all of the copied info including the file upload link from the original record.

    Long story short when I use the URL method I found your post prior that copies to the new record just fine using the button. When I use the Rich Text field instead, I get an error message when clicking the approve button. Seems like it's because the field may need to be formated, but I've tried a few things like URLEncode(), ToText(), but haven't figured out anything that works.


    ------------------------------
    Chris
    ------------------------------



  • 10.  RE: Formula Rich Text Button - File to Add Record

    Posted 07-01-2020 11:08
    What is the code in the button being clicked?  you seem to have a problem with the Approve button but have not provided that code.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Formula Rich Text Button - File to Add Record

    Posted 07-01-2020 13:50
    Sorry I didn't make it clear in my message above. I included only the copy field line. Since omitting this line the code works fine, but I've included it below.

    & "&_fid_300=" & [Deal Terms Documentation Link]

    Full button code below:

    var text URLONE=
    URLRoot() & "db/" & Dbid()
    & "?act=API_EditRecord&rid=" & [Record ID#]
    & "&_fid_183=" & URLEncode(UserToName(User()))
    & "&_fid_259=" & URLEncode(UserToName(User()))
    & "&_fid_233=true"
    & "&apptoken=///";

    var text AddRecord=
    URLRoot() & "db/" & [_DBID_TRANSACTIONS]
    & "?act=API_AddRecord"
    & "&apptoken=///"
    & "&_fid_6=" & [Application/Offer Confirmation Date]
    & "&_fid_7=" & [Rental or Sale?]
    & "&_fid_8=" & [Submission Date]
    & "&_fid_9=" & [Additional Agent Involved?]
    & "&_fid_10=" & [Category]
    & "&_fid_11=" & [Unit Number]
    & "&_fid_12=" & [Number of Bedrooms]
    & "&_fid_13=" & [Property Type]
    & "&_fid_14=" & [Square Footage]
    & "&_fid_15=" & [Lease Start Date]
    & "&_fid_124=" & [Client/Customer]
    & "&_fid_183=" & ToText([Transaction Approved By])
    & "&_fid_188=" & [Deal Type]
    & "&_fid_200=" & [Property Name]
    & "&_fid_201=" & [Property Address]
    & "&_fid_226=" & [Pipeline]
    & "&_fid_227=" & [HubSpot Deal ID]
    & "&_fid_283=" & [Sight Unseen]
    & "&_fid_284=" & [Preferred Method of Contact]
    & "&_fid_290=" & [Additional Agent Commission % Override By]
    & "&_fid_291=" & [Record ID#]
    & "&_fid_300=" & [Deal Terms Documentation Link]
    & "&_fid_293=" & Round([Additional Agent GCI],0.01)
    & "&_fid_316=" & ToNumber([Current Primary Repeat Increase]*100)
    & "&_fid_317=" & ToNumber([Current Secondary Repeat Increase]*100);

    var text DisplayRecord = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#];


    var text URL=
    $URLONE
    & "&rdr=" & URLEncode($AddRecord)
    & URLEncode("&rdr=" & URLEncode($DisplayRecord));

    //var text URL =
    //"javascript:" &
    //"$.get('" &
    //$CheckAndAdd &
    //"',function(){" &
    //"location.reload(true);" &
    //"});"
    //& "void(0);";

    If([Approved Confirmation]=true,

    "<a class='Vibrant Danger'>Approved</a>",

    "<a class='Vibrant Primary' style='border:1px solid #6BBD57; background-color:#6BBD57'" & "a href=" & $URL & ">Click to Approve</a>")

    ------------------------------
    Chris
    ------------------------------



  • 12.  RE: Formula Rich Text Button - File to Add Record

    Posted 07-01-2020 13:55
    try this

    & "&_fid_300=" & urlencode([Deal Terms Documentation Link])


    you are building a URL string and internet does not allow spaces in URLs ands certain characters do not transport properly in a URL.  So good practice is that when you are putting unpredictable characters into a URL (ie not A-Z or  0-9) , you should wrap them in URLEncode ( ..... )

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------