Forum Discussion

AndrewFry's avatar
AndrewFry
Qrew Assistant Captain
6 years ago

Listing Required fields not completed

I am in need of some brain power to figure out how to go about listing all required fields not completed.

For a Work Order to be deemed "Active", several different fields must be completed. These fields range from fields within the actual Work Order form, to fields from other tables related to the Work Order.

I understand that I can make these fields Required for data input by customizing the forms, but I don't think that will allow me to accomplish what I need.

If I block the work order from being submitted (record being generated), then I have won't be able to work with them in the system.

I can require a bare minimum of fields just to get the work order generated (record created), but I need some way of identifying which fields I am still lacking in order to deem the Work Order as active.

I need to create a report that lists which fields I am requiring to be filled in are currently not completed. As a field is completed, that list shrinks, only showing the ones that are not completed.

Example:

For data input, I will require a bare minimum of:
• Client Name
• Client Phone
• Client Email

This will allow the work order to be generated, but unless all the required fields are completed, it won't be in an Active status.

The other fields that are required to be completed (not for data input, but to be considered in an Active status) are:
• Location
• Area
• Date Scheduled
• Tech Scheduled
• etc ....

Not all of those fields are in the actual work order. Some are from various tables all related directly or indirectly to the Work Order.

Hopefully that makes sense.

Thank you to all of you for pointing me in the right direction.


------------------------------
Andrew
------------------------------
Forms
Formulas and functions
roles and permissions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Yes, that is a great technique.  A sure way to get hated around the company water cooler is to make users fill out 100 fields before they can save.  It's very unfair to the user experience.  So as you suggest, a better way is to put a field at the top of the form for warnings or exceptions - whatever you call it and then using a form rule do not allow them to Submit (ie flag the record is being ready for the next step) until they clear the warnings.  You can use form rules to hide the warning field if its blank to close up the space.

    Here is an example of such a warning. this would be a formula Rich Text field.

    var text Exceptions =

    List("<br>",

    If(Trim([Project Name])="","Missing Project Name"),

    If(Trim([Channel])="","Missing Channel"),

    If(Trim([Company])="","Enter your Company"),

    If(Trim([Branch])="", "Missing Branch"),

    If(Trim([Account#])="", "Missing Account#"),

    If(Trim([Territory Sales Rep Code])="", "Missing Sales Rep"),

    If(Trim([Project City, Prov])="", "Missing Project City, Prov"),

    If(Trim([Cost Centre])="", "Missing Cost Centre"),

    If(Trim([Attention:])="", "Missing Dealer Attention Name"),

    If(Trim([Attn. Email:])="", "Dealer Attention Email"),

    If(ToText([Suggested Resale to Dealer ($)])="0", "Missing Suggsted Resale to Dealer"),

    If(Trim([Volume Opportunity])="", "Missing Volume Opportunity Detail"),

    If(ToText([Competitor Price ($)])="0", "Missing Competitor Price"),

    If(ToText([Expected Ship Date])="", "Missing Expected Ship Date"),

    If(Trim([Delivery Terms])="", "Missing Delivery Terms"),

    If([# of Missing CSP Product Descriptions] > 0, "Some Attributes are Missing Descriptions"),

    If([Pictures or Samples?]="", "Please specify if sending Samples or Pictures in Attributes Section"),

    If( (Contains([Pictures or Samples?], "Both") or Contains([Pictures or Samples?], "Pictures")) and [# of Pictures Attached]=0, "Please attach Missing Pictures under Project Notes / Docs / Pictures Tab"),


    If([Sort of Status Choice]=7 and Nz([Price for Customer Acknowledgement Form])=0, "Price for Customer Acknowledgement is Missing"),

    If([Sort of Status Choice]=11 and ([Item # Setup by MFA])="", "Missing Product Code")

    )
    ;


    If(Trim($Exceptions) <>"" 
    "<font color=Blue><b>The submit button will appear in View mode when the Warnings are cleared</b><font color=Red><br>" & $Exceptions )

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • AndrewFry's avatar
      AndrewFry
      Qrew Assistant Captain
      6 years ago
      Mark,

      Exactly!!! Trust me, I don't want to be the topic of gossip around the water cooler!! There is enough of that already!! LOL

      Rather than requiring 100 fields be completed prior to being allowed to submit the record, I would rather require say 4 fields so that the record can at least be generated with a bare minimum of information and thus get in to the system. It would be considered "Pending" at that point because it doesn't have all of the required information to be "Active".

      Then I would want a report to list all work orders which are in a Non-Active (i.e. Pending) status and which have missing information.

      I can already generate a report listing all Pending Work Orders, and all other needed information. The issue I am having is how do I compile a list of all missing fields for each individual work order and present that in the report? Ideally I would want it to be a single column that lists each missing field all together in a single cell in the report for each work order. As each field is populated, the list shrinks for that particular work order.

      I like the idea of having a warning at the top of the form, but the user wouldn't see it unless they had that form open. So having both a report on the dashboard and the warning in the form I think would be the ideal way to go. What do you think?

      So for the warning field, based upon the fact that you provided some excellent code as an example (thanks for doing that!!!), I am assuming that the Warning field would need to be a Text Formula Field. Am I correct in that assumption?

      Thanks for the in depth response Mark and, again, for the snippet of code as an example.

      Also, which range is that behind you in your profile pic?

      ------------------------------
      Andrew Fry
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        The mountain range is in Switzerland (probably St. Moritz) and the Intuit jacket is back from the days when the Company had larger budgets for giving Christmas presents to Quick Base Solution providers. :)

        As for a report, just put that warning field on a report and filter the report where the warning field is not blank.  Yes, this is a formula Rich Text field if you want it to be in red, or if you just want plain text and not fancy, then make it a formula text field  and change the separator on the list function from 

        "<br>"  (that means in html to break to a new line)

        and change that to 

        "\n"   (which is the non HTML marker to break to a new line)

        The new line will make the individual missing fields list vertically.





        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        markshnier2@gmail.com
        ------------------------------
  • EverettPatterso's avatar
    EverettPatterso
    Qrew Captain
    6 years ago
    What type of field are you using for the Status? You should be able to make a formula text field as the status, and use that as a filter for the report. If the status is Inactive then show on report. 

    Status field (formula text)
    If(
    [Location]="", "Inactive",
    [Area]="", "Inactive",
    [Date Scheduled]="", "Inactive",
    [Tech Scheduled]="", "Inactive",
    "Active")


    ------------------------------
    Everett Patterson
    ------------------------------
    • AndrewFry's avatar
      AndrewFry
      Qrew Assistant Captain
      6 years ago

      Thanks for the response Everett.

      I have a text field as my status and it is currently being changed via automation once all required fields are completed. The automation is based upon an additional field that uses a formula similar to the one you suggested, but it is:

      If([Client Name] <> "" and [Subdivision Name] <> "" and [Location Address] <> "" and ([Contact Name] <> "" or [Related Contact2 - Contact Name] <> "" or[Related Contact3 - Contact Name] <> "" or [Related Contact4 - Contact Name] <> "" or [Related Contact5 - Contact Name] <> "" or [Related Contact6 - Contact Name] <> "" or [Related Contact7 - Contact Name] <>"") and [Total # of Jobs] >=1 and [Total # of Jobs]=[# of Scheduled Jobs] and [Type of Work Order] <> "" and [Is there a Closing/WO Deadline?] <> "","YES","NO")​



      However, if not all required fields are completed and the Work Order is submitted, the user has no idea which field or fields is/are currently missing information so that the Work Order can move automatically to the Active status. They would have to go back through the different tabs, forms, etc., to visually identify which field is missing information. This is not fair to the user.

      The suggestion you provided would allow me to change my Status based upon if a required field is blank or not.

      Do you have a suggestion as to how I can identify which fields are missing information so that I can rely that to the user?

      Thanks again for your input!



      ------------------------------
      Andrew Fry
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        .. see my response below ..

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