Discussions

Expand all | Collapse all

Listing Required fields not completed

  • 1.  Listing Required fields not completed

    Posted 29 days ago
    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
    ------------------------------


  • 2.  RE: Listing Required fields not completed

    Posted 28 days 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
    ------------------------------



  • 3.  RE: Listing Required fields not completed

    Posted 28 days ago
    Edited by Andrew Fry 28 days 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
    ------------------------------



  • 4.  RE: Listing Required fields not completed

    Posted 28 days ago
    .. see my response below ..

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



  • 5.  RE: Listing Required fields not completed

    Posted 28 days ago
    Mark,

    Yes, I saw your response. I actually responded to yours before I did to Everett's. Strange that yours is at the bottom of the conversation .....

    I would think that it should be in chronological order ..... but it appears to be in order of response to the order others responded ... strange ...

    See my response below your original one. :)

    ------------------------------
    Andrew Fry
    ------------------------------



  • 6.  RE: Listing Required fields not completed

    Posted 28 days ago
    Edited by Mark Shnier (YQC) 28 days 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
    ------------------------------



  • 7.  RE: Listing Required fields not completed

    Posted 28 days 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
    ------------------------------



  • 8.  RE: Listing Required fields not completed

    Posted 28 days 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
    ------------------------------



  • 9.  RE: Listing Required fields not completed

    Posted 28 days ago
    Fantastic!! Thanks for the info.


    ------------------------------
    Andrew Fry
    ------------------------------



  • 10.  RE: Listing Required fields not completed

    Posted 28 days ago
    Edited by Andrew Fry 28 days ago
    Mark,

    I just wanted to say thanks again for the pointers. They have gotten me started in the right direction.

    I started to implement it and it has really helped. I did have to set up a number of summary fields (like 10 - 15 of them) in order to capture children and grandchildren fields being completed.

    Example:

    I have a work order which has many jobs. Each job has many different fields that need to be completed, as well as many assignments. Each assignment has many different fields which need to be completed.

    In order to see if all of the required fields in the assignments are completed, I generated a summary field in the Jobs table to count how many assignments there are. I then have a summary field for each required field to count how many do not have blanks. I then evaluate in a formula the number of assignments for that job compared to the number of non-blank fields for that particular field. (e.g. # of Assignments vs # of Dates Scheduled). If they are the same, then I know that all of my assignments have corresponding Dates Scheduled. If they are not the same, then I have the Exceptions populate that field's title into the list.

    However, this seems rather cumbersome to me and not very elegant. I have to do a comparison of ​every "required" assignment field to the total number of assignments, for each job, to identify if any of the assignment fields are missing.

    Then I have to do the same thing for all of the jobs to identify if any of the "required" job fields are missing.

    There has to be a better, more efficient, more elegant, less cumbersome way of doing this.

    The code example you gave me works great, and works the way it is supposed to.

    I just think that my implementation of it is not ideal.

    What are everyone's thoughts?

    ------------------------------
    Andrew
    ------------------------------



  • 11.  RE: Listing Required fields not completed

    Posted 28 days ago
    I don't think that there is a more elegant way, if you really do need that level of detail rolled up to higher levels. Normally, the need is to identify the missing fields on a single record in a single earning message.

    The "normally" you might want to roll up how many child records have warnings.  ie where the warming field is not blank.

    But if your need is to provide at a parent level the details of how many child record are missing which required fields, field by field, then it's a lot of work and not something that any "normal" computer system would attempt.

    But if it is a legitimate business need then all you can do is to get in an undisturbed mode either with music or quiet - whatever works for you and just hunker down and grind away at that until its all done.

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



  • 12.  RE: Listing Required fields not completed

    Posted 28 days ago
    " then all you can do is to get in an undisturbed mode either with music or quiet - whatever works for you and just hunker down and grind away at that until its all done. "

    Yes, very true!!! That one made me chuckle a bit.

    I am working through it all right now and it is definitely a grind .... I have run into a few snags, but I think I will tackle those once I know the other items are working.

    Thanks again Mark!




    ------------------------------
    Andrew Fry
    ------------------------------



  • 13.  RE: Listing Required fields not completed

    Posted 28 days ago
    So following your examples, I have a few lines of code that for the most part work.

    If(Trim([Is there a Closing/WO Deadline?])="","Unanswered Closing/WO Deadline Question"),
    If(Trim([Is there a Closing/WO Deadline?])="YES" and ToText([Closing/WO Deadline])="","No Closing/WO Deadline Date"),​

    The first line works great! Once I select either YES or NO from the dropdown, then "Unanswered Closing/WO Deadline Question" is eliminated from the Exceptions list. When I clearout the field, then it returns to the exceptions list.

    The [Is there a Closing/WO Deadline?] field is a Text-Multi Choice field, with either NO or YES as the options.

    Via a form rule, when YES is selected, then the [Closing/WO Deadline] field appears in the form. It is a Date field.

    However, the second line of code above doesn't seem to be working properly.
    Once YES is selected, then the "Unanswered Closing/WO Deadline Question" should be eliminated from the exceptions list, and if the date is blank, then the "No Closing/WO Deadline Date" should appear. It isn't.

    I have several other fields similar to this one, where they are form rule dependant on an YES or a NO.

    What could be causing the issue? It appears to me that it is stemming from the fact that it is hidden via the form rule, but I am not sure.

    It could also be that I simply wrote something incorrectly ... although all the rest of the code is working as expected so far.


    ------------------------------
    Andrew Fry
    ------------------------------



  • 14.  RE: Listing Required fields not completed

    Posted 28 days ago
    Is that that you complete formula?  if so it should read

    List("\n",
    If(Trim([Is there a Closing/WO Deadline?])="","Unanswered Closing/WO Deadline Question"),
    If(Trim([Is there a Closing/WO Deadline?])="YES" and ToText([Closing/WO Deadline])="","No Closing/WO Deadline Date")
    )



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



  • 15.  RE: Listing Required fields not completed

    Posted 28 days ago
    Edited by Andrew Fry 28 days ago
    No, the complete formula is this:

    var text Exceptions =

    List("<br>",
    If(Trim([Client Name])="","No Client Selected"),
    If(Trim([Contact Type])="" and Trim([Related Contact2 - Contact Type])="" and Trim([Related Contact3 - Contact Type])="" and Trim([Related Contact4 - Contact Type])="" and Trim([Related Contact5 - Contact Type])="" and Trim([Related Contact6 - Contact Type])="" and Trim([Related Contact7 - Contact Type])="","No Contacts Selected"),
    If(Trim([Subdivision Name])="","No Subdivision Selected"),
    If(Trim([Location Address])="","No Address Selected"),
    If([Total # of Jobs]<1,"No Jobs"),
    If([Total # of Jobs]>=1 and [# of Assignments]=0,"No Assignments"),
    If(Trim([Type of Work Order])="","Type of Work Order not Indicated"),
    If(Trim([Is there a Closing/WO Deadline?])="","Unanswered Closing/WO Deadline Question"),
    If(Trim([Is there a Closing/WO Deadline?])="YES","No Closing/WO Deadline Date"));

    If(Trim($Exceptions)<>"","<font color=Blue><b>Below is a list of fields that are missing data to move the Work Order to an Active status:</b><font color=Red><br>"&$Exceptions)


    I seem to be having the same issue with this line of code:

    If([Total # of Jobs]>=1 and [# of Assignments]=0,"No Assignments"),

    I don't see to be following the syntax correctly or something.

    I still have several more lines to add for the various Job fields and Assignment fields, but I think I am going to run into the same issue as those two lines that are causing me problems right now.

    ------------------------------
    Andrew
    ------------------------------



  • 16.  RE: Listing Required fields not completed

    Posted 28 days ago
    I'm a bit confused if you are saying that the form rules are not working correctly or if you are trying to debug a formula.
    One way to debug a long formula is to make a temporary test field for just one line like this one.

    If(Trim([Is there a Closing/WO Deadline?])="YES" and ToText([Closing/WO Deadline])="","No Closing/WO Deadline Date"),

    and then look at an example record and see what is actually in those fields.  that has nothing to do with form rules, just what the record data shows in view mode.


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



  • 17.  RE: Listing Required fields not completed

    Posted 28 days ago
    Sorry about the confusion.

    I was simply stating that I thought that perhaps the issue was stemming from the Form rules, and causing conflict with the formula.

    The form rules are working correctly.

    I will have to test the individual lines that are not working correctly to debug them, as you recommended.

    At first glance, does anything stand out to you as being incorrect or wrong with the lines of code?

    I will research on my end and debug individually.

    Thanks for your input during this whole thing.

    I appreciate it.


    ------------------------------
    Andrew Fry
    ------------------------------



  • 18.  RE: Listing Required fields not completed

    Posted 26 days ago
    Ok, I think I figured it out.

    I didn't realize that the "Yes" or text was case sensitive.

    I am testing a few other examples but I think I got it!!

    ------------------------------
    Andrew Fry
    ------------------------------



  • 19.  RE: Listing Required fields not completed

    Posted 26 days ago
    It is sometimes confusing when QuickBase is or is not case sensitive.

    but in a formula with = it is case sensitive

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



  • 20.  RE: Listing Required fields not completed

    Posted 26 days ago
    If you want to eliminate the ambiguity around case sensitivity in formulas, you can always use the Lower() and Upper() functions. It then won't matter if your field has the word "yes" spelled with any of the 8 combinations of upper and lower case letters that can form that word (e.g. "yes", "YES", "YeS", "yEs"), Upper([My Field]) will always be "YES" and Lower([My Field]) will always be "yes".

    ------------------------------
    -Tom
    ------------------------------



  • 21.  RE: Listing Required fields not completed

    Posted 26 days ago
    That is a great idea!!

    I already implement that with a number of Excel apps I developed, so I don't know why I didn't think of that, lol.

    Thanks for the great tip Tom!!

    ------------------------------
    Andrew Fry
    ------------------------------