Forum Discussion

LizChartrand's avatar
LizChartrand
Qrew Cadet
2 months ago
Solved

If/Then Help - Unforeseen number populating

Hello!

I've got an API button that when triggered, creates a new record with some preset information pulled in.
That all works except in the instances where I need some fields to be blank.
I thought I could use an If/Then statement to complete data or stay blank when necessary.  for exp,

If( [Renewed Parking rent] >0, [Renewed Parking rent] ), [...] 

I assumed if I left that as the statement, nothing would happen in the even of a $0 amount. The new record's field would be intentionally blank.

 Instead strangely, when data should be blank (whether the if/then sample above is blank or null), it populates a crazy amount like $25,000... It's always roughly $2#,###.

I've attached some of the screenshots of the data output (currency fields) and formula. 

The text fields populate correctly if for example, [Renewal OutcomeStorage] (currency) =0 so the output for [Storage] = No (row 32) but [Storage Rent] (currency) should be blank but instead reflects $24,302.00.

Any ideas about what I'm doing wrong?

 

Thank you for your help,

Liz

 

formulas
  • MarkNishikawa's avatar
    MarkNishikawa
    1 month ago

    What if you exclude the field reference line completely from the URL if there is no data in [Renewal Outcome Parking Rent]?

    & If([Renewal Outcome Parking Rent]>0, "&_fid_176=" & [Renewal Outcome Parking Rent], "")

11 Replies

  • LizChartrand's avatar
    LizChartrand
    Qrew Cadet
    2 months ago

    Sure thing, Mark,

     

    var text Renewed=

    URLRoot() & "db/" & [_DBID_***]

    & "?a=API_GenAddRecordForm"

    &"&apptoken=***"

    & "&_fid_8=" & [Related Application]

    & "&_fid_328=" & URLEncode(If([Property - Invoice for Renewals?]="Yes","No","Yes"))

    & "&_fid_330=" & URLEncode([Property - Invoice for Renewals?])

    & "&_fid_141=" & "1"

    & "&_fid_143=" & "2"

    & "&_fid_895=" & [What Terms Need to Be Added?]

    & "&_fid_781=" & [Related Property*]

    & "&_fid_138=" & [Related Building]

    & "&_fid_6=" & [Related Renewal Unit]

    & "&_fid_69=" & [Renewal Outcome Gross Rent]

    & "&_fid_45=" & URLEncode([Move-In])

    & "&_fid_39=" & URLEncode([Renewal Outcome Lease Start])

    & "&_fid_52=" & URLEncode([Renewal Outcome Lease End])

    & "&_fid_40=" & [Renewal Outcome Lease Term]

    & "&_fid_226=" & [Renewal Unit Utility Fee]

    & "&_fid_57=" & If([Renewal Outcome Parking Rent]>0,"Yes","No")

    & "&_fid_176=" & If([Renewal Outcome Parking Rent]>0,[Renewal Outcome Parking Rent],null)

    & "&_fid_67=" & If([Renewal Outcome Pet]>0,"Yes","No")

    & "&_fid_64=" & If([Renewal Outcome Pet]>0,[Pet Details], null)

    & "&_fid_65=" & If([Renewal Outcome Pet]>0,[Pet Fee], null)

    & "&_fid_66=" & If([Renewal Outcome Pet]>0,[Pet Rent],null)

    & "&_fid_71=" & If([Renewal Outcome Storage]>0,"Yes","No")

    & "&_fid_72=" & If([Renewal Outcome Storage]>0,[Renewal Outcome Storage],null)

    & "&_fid_22=" & [Renewal Outcome Concession (mths)]

    & "&z=" & Rurl();

    var text Renewals =

    $Renewed;

    //button colors

    //whitetext on orange button

    var text bgcolorone = "#dc7c5c";

    var text txtcolorone = "white";

    var text styleone =

    "style=\"text-decoration: none; width: 150px; text-align: center; background:" & $bgcolorone & "; border-radius: 5px; padding: 8px 20px; color: " & $txtcolorone & "; display: inline-block; font: normal 700 24px/1 \"Calibri\", sans-serif; text-shadow: none;";

    Case([What Terms Need to Be Added?],

    "Renewed",

    "<a class='SaveBeforeNavigating'" & $styleone & " href = '" & $Renewals & "'> Add "&[What Terms Need to Be Added?] & " </a>",

    "Renewed (Transfer)" ,

    "<a class='SaveBeforeNavigating'" & $styleone & " href = '" & $Renewals & "'> Add "&[What Terms Need to Be Added?] & " </a>",

    "<span style='font-family: Arial, Helvetica, sans-serif; font-size: 10pt; text-align: right; color: #426ab3'>" & " Please select the type of terms to add to this application </span>")

  • Roy-Wanyoike's avatar
    Roy-Wanyoike
    Qrew Captain
    2 months ago

    Hello Liz, I don't know if that works but I would do it this way.
     If([Renewal Outcome Parking Rent]>0, [Renewal Outcome Parking Rent], "")



    What I did is use the "" instead of null because when the API action is done and url built it would probably give a random number like the one you're seeing. defining it as a blank string would resolve this. Additionally I would also create variables to ease the formula readability. I hope that solve it

    • LizChartrand's avatar
      LizChartrand
      Qrew Cadet
      2 months ago

      Thank you Roy, 

      I believe I had tried that and had similar results. I chalked it up to the field I was trying to populate being a currency numeric field. I'll try again just to be sure!

      • MarkNishikawa's avatar
        MarkNishikawa
        Quickbase Staff
        1 month ago

        What if you exclude the field reference line completely from the URL if there is no data in [Renewal Outcome Parking Rent]?

        & If([Renewal Outcome Parking Rent]>0, "&_fid_176=" & [Renewal Outcome Parking Rent], "")