Conditional API Call

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have two tables, Timesheets and Days. Each timesheet record contains fields for a total of 14 days. I have created a URL Formula button that will take certain fields, (Job #, Timesheet #, Period End Date, Date, Pay Amount and User Name) and create the individual day records in the Days table from this data.

My desire is to have the formula first check to see if any information is entered for a given day by checking the Pay field to make certain it's not blank. If it's not, the day record will be created and the formula will move on to check and create the subsequent days.

I have included 2 lines of the formula that I came up with below. It works perfectly until it hits the first false statement, at which point I get an improperly terminated API call and no more day records are created. Again, the formula only works if there is Pay information for BOTH of the days. I'd like to check and create all days with pay information and return to the timesheet record when complete.

I could use some help, thanks!

If([PAY 1]<>0,URLRoot()&"db/"&"abcdefg"&"?act=API_AddRecord"&"&_fid_14="&[JOB 1]&"&_fid_11="&[Timesheet#]&"&_fid_9="&[PE]&"&_fid_19="&[DATE 1]&"&_fid_13="&[PAY 1]&"&_fid_10="&UserToName([NAME],"FF")&

If([PAY 2]<>0,"&rdr="&URLEncode(URLRoot()&"db/"&"abcdefg"&"?act=API_AddRecord"&"&_fid_14="&[JOB 2]&"&_fid_11="&[Timesheet#]&"&_fid_9="&[PE]&"&_fid_19="&[DATE 2]&"&_fid_13="&[PAY 2]&"&_fid_10="&UserToName([NAME],"FF")&

"&rdr="&URLRoot()&"db/"&Dbid()&"abcdefg"&[Timesheet#])))

Photo of Royce

Royce

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
I recently answered a similar question.

My suggested work-a round was to use this format,.

$URLONE 


& "&rdr=" & URLEncode($URLTWO)
& URLEncode("&rdr=" & URLEncode($URLTHREE))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))
& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSEVEN))))))


Then above that code, you calculate the values for each URLxxx with an IF statement.  the URLxxx will either be adding a day, or else it can set a dummy checkbox to be true if you do not want anything actually done.  Who care if a dummy checkbox gets set to be true a bunch of times.
Photo of Royce

Royce

  • 0 Points
Thanks so much Mark, as usual, you have the right answer! You've also helped me to clean up my code, as I never used variables before. Everything is working perfectly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Thx for letting me know.  Good for you for figuring out I was using Formula Variables as I never explicitly said that.  They are super helpful to write tidy code and not waste even more time debugging formula syntax errors.  Or at least spotting so much quicker when they don't get past the formula box syntax checker.
Photo of Eric

Eric

  • 40 Points
Here's a slightly simpler example with all of the extra URLEncodes in case anyone needs this in the future:

var text AddRecordOne = URLRoot() & "db/" & [_DBID_TABLEALIAS] & "?a=API_AddRecord&_fid_6=" & [Revenue Start Date] & "&_fid_7=" & [Monthly Amount] & "&_fid_8=" & [Record ID#] & "&rdr=";

var text AddRecordTwo = URLRoot() & "db/" & [_DBID_TABLEALIAS] & "?a=API_AddRecord&_fid_6=" & [Revenue Start Date] & "&_fid_7=" & [Monthly Amount] & "&_fid_8=" & [Record ID#] & "&rdr=";

var text AddRecordThree = URLRoot() & "db/" & [_DBID_TABLEALIAS] & "?a=API_AddRecord&_fid_6=" & [Revenue Start Date] & "&_fid_7=" & [Monthly Amount] & "&_fid_8=" & [Record ID#] & "&rdr=";

var text AddRecordFour = URLRoot() & "db/" & [_DBID_TABLEALIAS] & "?a=API_AddRecord&_fid_6=" & [Revenue Start Date] & "&_fid_7=" & [Monthly Amount] & "&_fid_8=" & [Record ID#] & "&rdr=";

var text AddRecordFive = URLRoot() & "db/" & [_DBID_TABLEALIAS] & "?a=API_AddRecord&_fid_6=" & [Revenue Start Date] & "&_fid_7=" & [Monthly Amount] & "&_fid_8=" & [Record ID#] & "&rdr=";

var text displayRecord = URLRoot() & "db/" & Dbid() & "?a=q&qid=1000000";



$AddRecordOne

& URLEncode($AddRecordTwo

& URLEncode($AddRecordThree

& URLEncode($AddRecordFour

& URLEncode($AddRecordFive

& URLEncode($DisplayRecord)))))