Forum Discussion

KimG's avatar
Qrew Cadet
2 years ago

Can a button contain an if/case statement to open a specific form?

Is it possible to use an IF/CASE statement within a Button to select a specific Form to open?

I've been using 3 different forms for data entry, each with a lot of form rules, but I've run out of form rules so I think I need to create more forms to get around this limitation.  My issue is how can I get from the previous data entry record/form to the right form.  Right now, you get to those forms by clicking a button on another form from a different table that is the parent of the record created on my complicated table.

I'm guessing instead of 3 forms with complicated dynamic rules, I really to use a lot more forms for data entry. 

However, I don't want to create more buttons. The data entered on the form before clicking the button should be sufficient to dictate the next step.


Right now, one of my buttons that looks like this:



& "?a=API_GenAddRecordForm&_fid_9="

& URLEncode ([Record ID#])


& "&_fid_37=" & URLEncode([Juice - Record ID# of Current Vendor Inspection])

& "&z=" & Rurl()


How would I add an If/case statement to this? Is that possible?  What would the syntax look like? 


I would somehow need to reference the field selected [Related Juice] Field ID 18 that is selected on the form so that instead of:



I would need it to be something like:

If case [Related Juice]="ST9004", then &"&dfid=15",

If[Related Juice]="ST9003", then &"&dfid=16"




To give you an idea of what's going on here, let me try to give you a little background into the app. I've changed it a little in my attempts to make it easier to understand. It's a QA testing application:

The story:

We are supplied with juice that is tested and data recorded for quality assurance.  When a batch of juice arrives:

1.       It comes with some data we need to enter (call this: Vendor data)

2.       We take a few test samples and enter this data (call this: Initial data)

3.       We process it and take a few more tests and enter this data (call this: In-process data)

All of this test data is entered in the same table, but the three types of data entered (Vendor, Initial and In-process) have some of the same fields like the sugar content, but others are different. It also is different for each Juice (for example we may only test the % pulp content for orange and peach juice, but not for apple or grape). (This is why I have a lot of form rules.  The three buttons I have now are for "Vendor," "Initial" and "In-process" data entry)

The Tables:

·         Juice Table: Records include: apple, grape, orange, peach, etc
·         Incoming Inspection Table: Record for each time we receive a delivery of juice; date of delivery, supplier, accept/reject, etc.
·         Size Analysis Inspections Table: Record for each time enter test data such as sugar content, pulp content, color, etc.

o   This is where the "Vendor data," "Initial Data" and "In-process data are all recorded currently using 3 separate forms for data entry

o   This is where I'm out of form rules because I have about 20 types of Juice and 50 data fields. Many data fields are used for several or all juices, others are used for just one type of juice or one type of inspection (vendor, initial or in-process).

·         Requirements Table: This table relates to both the Juice and Size Analysis table. It tells us which fields we need to record for each juice type and inspection type (vendor, initial, in-process). It also tells us when we enter data in the Size Analysis Table if is in the correct range or not. (For example, if we receive Apple juice and enter the Vendor data sugar content of 10% in the Size inspections table, but the Requirement record for that type of juice is 12-15% it will warn us that it's not sweet enough.)

The Workflow:

Example 1: We get a batch of Apple juice:

1.       Click the button to enter a new "Incoming Inspection" record on the Incoming inspection table. 

2.       Fill out some data (e.g. date received, weight, # of gallons)

3.       Relate it to "Apple" from the Juice Table. 

4.       Click a button to enter a record on the Size Analysis Inspections table to record the Vendor data or the Initial data.

Example 2: We process a batch of Apple juice previously received and already recorded

1.       Open the Record for the Apple Juice received that has just been processed

2.       Click the button to enter a new "In-process Inspection" record

3.       Fill out the data

Also, to note on my real system, I have about 20 types of "Juice."  There are about 50 data fields in total, but most only have data recorded for about 5 of them.  There are a couple of field that are used on almost all the forms, but others that may only be used exclusively for one type of "Juice."

Finally, if I simply can't use an if/case statement in button, are there other suggestions for how to improve this workflow? 

I'm guessing from the recent announcements about new forms coming, that this might be something I need to wait a bit to see if the new forms might be a solution.

Kim G

3 Replies

  • Yes, you can certainly have URL formula buttons to view or edit on a particular form, but I really recommended against this practice.  The problem will be endless user frustration with the from opening up for view or edit on the "wrong" form, causing them to need to push a button on the form to switch to the correct form  and in fact two buttons needed for every form as the user may want to View or Edit.  Then after Edit, the form is likely to save and redisplay to the user on the "wrong" form.

    So my suggestion is to make one form with form rules to show / hide ideally whole sections or whole Tabs and then place the common data entry field sin a section at the top of the form. 

    So I hesitate answering your direct question as it's going down a path which will lead to user frustration.  But if you really want to know, you can post back and I will reluctantly answer.  :) 

    Mark Shnier (Your Quickbase Coach)
    • KimG's avatar
      Qrew Cadet

      Hi Mark,

      The button is only clicked for data entry, creating a child record. If the button has the case in it, why would it lead to the wrong form?

      I tried using Sections, which I think would work if I could make the field show up twice on the form such that it only appears once because sections are hidden based on a form rule.  But although you can add some fields twice to the form, you can't add all types of fields multiple times to the form.  Even then, I still might run out of form rules.

      There are 19 types of Juice and about 50 fields for data entry.  There are just too many combinations with just enough overlap in fields to make it super complicated.

      I really wish I could just add some more form rules.

      Kim G
      • MarkShnier__You's avatar
        Icon for Qrew Legend rankQrew Legend

        There is no practical limit to the number of form rules as long as they are based on scalar (data entry) fields for the rule. But you will need 50 rules, once for each field.

        So, if you do want to go with multiple forms and you have a use case where  you are creating child records, then yes, you can make an Add Child button on the Parent record which will use the correct form to Add.

        I suggest that you use make a formula text field on the Parent called 
        [Child Form Number] 

        Case([Juice Type],
        "Type 1", "11", // these are the form ID's on the forms list.
        "Type 2", "12",
        "Type 3", "13",
        "Type 20", "31")

        The Add Child button can look like this

        URLRoot() & "db/" & [_DBID of the child table]
        & "?act=GenAddRecordForm"
        & "&_fid_xx=" & [Record ID#] // populate Related Parent
        & "&dfid=" & [Child Form Number] // set the correct form

        Then similarly, you would lookup the [Child Form Number] down to the child record.  Then make two fields on the Child form for View ad edit.

        URLRoot() & "db/" & dbid() & "?a=dr&rid=" & [Record ID#]
        & "&dfid=" & [Child Form Number]

        URLRoot() & "db/" & dbid() & "?a=er&rid=" & [Record ID#]
        & "&dfid=" & [Child Form Number]  

        Then for the embedded child table on the parent form, configure it to have those two fields as the first two columns of the report and edit the report so as to deselect Allow View and Allow Edit.  That will suppress the native eyeball and edit pencil icons on that embedded report, forcing the user to use your buttons which will select the correct form.

        Mark Shnier (Your Quickbase Coach)