Help with Formula

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

I create an app in QB which is a check in/check out system using a scanner.  I have setup 12 check in and 12 check out fields.  I also have a formula field that if the fields are blank the status is "requested" and as soon as items are in the check out field the status changes to "Shipped" and when the item is checked in the status changes to "Received"  The problem I have is I can only make the status field change based off the first check in and check out fields.  So if I have 6 items checked out and I check in 4 of them the status shows as received because the first line is tied to the formula.  The formula I am using is below

If(
Trim([Test Kit (1) - Check In]) <>"", "Returned",
Trim([Test Kit (1) - Check Out])<>"", "Shipped/With Publisher", "Requested")

How do I make this formula work with the 11 other fields that need this formula as well?  I tried changing to

If(
Trim([Test Kit (2) - Check In]) <>"", "Returned",
Trim([Test Kit (2) - Check Out])<>"", "Shipped/With Publisher", "Requested")

and so on until I hit 12 but it didn't work.  Any ideas?
Photo of Derek

Derek

  • 20 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
So the "system" record has only 1 status, but that status in dependent on various components being checked out and then ALL checked back in again before the Status can be back to checked in.  I will also assume that as soon as line 1 is checked out, that the Status would move to Checked out. 

var text LineOne = if(

Trim([Test Kit (1) - Check In]) <>"", "Returned",

Trim([Test Kit (1) - Check Out])<>"", "Shipped/With Publisher", "Requested");


var text LineTwo = if(

Trim([Test Kit (2) - Check In]) <>"", "Returned",

Trim([Test Kit (2) - Check Out])<>"", "Shipped/With Publisher", "Requested");


etc var text LineTwelve = if(

Trim([Test Kit (12) - Check In]) <>"", "Returned",

Trim([Test Kit (12) - Check Out])<>"", "Shipped/With Publisher", "Requested");


IF(LineOne="Requested", "Requested",  // the System is Requested if Line 1 is requested
(LineOne="Shipped/With Publisher" or LineTwo="Shipped/With Publisher"
oretc...LineTwelve="Shipped/With Publisher"), "Shipped/With Publisher", "Returned")
Photo of Derek

Derek

  • 20 Points
Is this all one formula or two different ways of doing it?  Do I need to start the formula as var text Line One and Two etc? I get an error when I cut and paste the var text formula into my field.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
This is all one formula.  It makes use of formula variables which you can ad about here.
http://www.quickbase.com/user-assistance/Default.html#formula_variables.html

Give it a try and let me know how you make out.  If the formula has syntax errors in it, please post the formula and the syntax error, as I have not tested the formula.
Photo of Derek

Derek

  • 20 Points
Here is my formula I am getting syntax errors.

var text LineOne = if(
Trim([Test Kit (1) - Check In]) <>"", "Returned",
Trim([Test Kit (1) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineTwo = if(
Trim([Test Kit (2) - Check In]) <>"", "Returned",
Trim([Test Kit (2) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineThree = if(
Trim([Test Kit (3) - Check In]) <>"", "Returned",
Trim([Test Kit (3) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineFour = if(
Trim([Test Kit (4) - Check In]) <>"", "Returned",
Trim([Test Kit (4) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineFive = if(
Trim([Test Kit (5) - Check In]) <>"", "Returned",
Trim([Test Kit (5) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineSix = if(
Trim([Test Kit (6) - Check In]) <>"", "Returned",
Trim([Test Kit (6) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineSeven = if(
Trim([Test Kit (7) - Check In]) <>"", "Returned",
Trim([Test Kit (7) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineEight = if(
Trim([Test Kit (8) - Check In]) <>"", "Returned",
Trim([Test Kit (8) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineNine = if(
Trim([Test Kit (9) - Check In]) <>"", "Returned",
Trim([Test Kit (9) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineTen = if(
Trim([Test Kit (10) - Check In]) <>"", "Returned",
Trim([Test Kit (10) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineEleven = if(
Trim([Test Kit (11) - Check In]) <>"", "Returned",
Trim([Test Kit (11) - Check Out])<>"", "Shipped/With Publisher", "Requested");
var text LineTwelve = if(
Trim([Test Kit (12) - Check In]) <>"", "Returned",
Trim([Test Kit (12) - Check Out])<>"", "Shipped/With Publisher", "Requested");
IF(
LineOne="Requested", "Requested",  // the System is Requested if Line 1 is requested
(LineOne="Shipped/With Publisher"or
LineTwo="Shipped/With Publisher"
 or
LineThree="Shipped/With Publisher"
 or
LineFour="Shipped/With Publisher"
 or
LineFive="Shipped/With Publisher"
 or
LineSix="Shipped/With Publisher"
 or
LineSeven="Shipped/With Publisher"
 or
LineEight="Shipped/With Publisher"
 or
LineNine="Shipped/With Publisher"
 or
LineTen="Shipped/With Publisher"
 or
LineEleven="Shipped/With Publisher"
or
LineTwelve="Shipped/With Publisher"), "Shipped/With Publisher", "Returned")

You see anything out of place?

Thank you.
Photo of Derek

Derek

  • 20 Points
Formula syntax error

The argument list of the function LineOne must begin with a left parenthesis.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
We were missing the $ sign in front of the formula variable when it is used in formuals.  I added them in.


var text LineOne = if(
Trim([Test Kit (1) - Check In]) <>"", "Returned",
Trim([Test Kit (1) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineTwo = if(
Trim([Test Kit (2) - Check In]) <>"", "Returned",
Trim([Test Kit (2) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineThree = if(
Trim([Test Kit (3) - Check In]) <>"", "Returned",
Trim([Test Kit (3) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineFour = if(
Trim([Test Kit (4) - Check In]) <>"", "Returned",
Trim([Test Kit (4) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineFive = if(
Trim([Test Kit (5) - Check In]) <>"", "Returned",
Trim([Test Kit (5) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineSix = if(
Trim([Test Kit (6) - Check In]) <>"", "Returned",
Trim([Test Kit (6) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineSeven = if(
Trim([Test Kit (7) - Check In]) <>"", "Returned",
Trim([Test Kit (7) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineEight = if(
Trim([Test Kit (8) - Check In]) <>"", "Returned",
Trim([Test Kit (8) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineNine = if(
Trim([Test Kit (9) - Check In]) <>"", "Returned",
Trim([Test Kit (9) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineTen = if(
Trim([Test Kit (10) - Check In]) <>"", "Returned",
Trim([Test Kit (10) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineEleven = if(
Trim([Test Kit (11) - Check In]) <>"", "Returned",
Trim([Test Kit (11) - Check Out])<>"", "Shipped/With Publisher", "Requested");

var text LineTwelve = if(
Trim([Test Kit (12) - Check In]) <>"", "Returned",
Trim([Test Kit (12) - Check Out])<>"", "Shipped/With Publisher", "Requested");

IF(
$LineOne="Requested", "Requested",  // the System is Requested if Line 1 is requested
($LineOne="Shipped/With Publisher"or
LineTwo="Shipped/With Publisher"
 or
$LineThree="Shipped/With Publisher"
 or
$LineFour="Shipped/With Publisher"
 or
$LineFive="Shipped/With Publisher"
 or
$LineSix="Shipped/With Publisher"
 or
$LineSeven="Shipped/With Publisher"
 or
$LineEight="Shipped/With Publisher"
 or
$LineNine="Shipped/With Publisher"
 or
$LineTen="Shipped/With Publisher"
 or
$LineEleven="Shipped/With Publisher"
or
$LineTwelve="Shipped/With Publisher"), "Shipped/With Publisher", "Returned")
Photo of Derek

Derek

  • 20 Points
Thanks still getting syntax error:

The argument list of the function LineTwo must begin with a left parenthesis.

Also, do I need to check the "Allow some HTML tags to be inserted in the field"
Photo of Derek

Derek

  • 20 Points
I got it to work, thank you very much
Photo of Derek

Derek

  • 20 Points
So my next issue with this app is creating dynamic form rules around the check in and check out fields.  The rules I am trying to do is If "Test Kit 1 - Check Out" doesn't match "Test Kit 1  - Check In", I want the system to display a message or turn the value red or something stating the two items do not match. Any ideas on how to do this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
Great, glad to hear it worked.

I suggest using formula fields for that.

IF([Test Kit (1) - Check In]) <>"" and Trim([Test Kit (1) - Check Out]<>"" and
[Test Kit (1) - Check In]) <>Trim([Test Kit (1) - Check Out], "<b><font color=red>Check Out / Check in serials do not match")

and enable html so that it shows up in red.
Photo of Derek

Derek

  • 20 Points
Thanks again, I created a new formula field called "Test Kit 1 Validation" which I will hide but when I put the formula in there I get this syntax error:

The types of the arguments or the number of arguments supplied do not meet the requirements of the function If.

The function is defined to be If (Boolean condition1, result1, ..., else-result).
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
Here is a better formula
var text CI = Trim([Test Kit (1) - Check In]);
var text CO = Trim([Test Kit (1) - Check Out]);

IF($CI<>"" and $CO<>"" and $CI<>$CO, "<b><font color=red>Check Out / Check in serials do not match")
Photo of Derek

Derek

  • 20 Points
Great! that worked thank you.  If the serial numbers do match is there a way to add to current formula like a green checkbox?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
There are 502 icons here in 5 sizes, so 2,500+ icons. That is a bookmark worth saving :)

https://www.quickbase.com/db/9kaw8phg?a=ShowPage&pageid=134

Here is a formula with an icon
var text CI = Trim([Test Kit (1) - Check In]);
var text CO = Trim([Test Kit (1) - Check Out]);

IF(
$CI<>"" and $CO<>"" and $CI<>$CO, "<b><font color=red>Check Out / Check in serials do not match",
$CI<>"" and $CO<>"" and $CI=$CO, "<img src=https://images.quickbase.com/si/24/211-check_all.png>")
Photo of Derek

Derek

  • 20 Points
Again thank you very much and I will book mark that page.