Forum Discussion

RyanAnderson2's avatar
RyanAnderson2
Qrew Cadet
6 years ago

Using a Formula, be able to search for a value in a field across all records of a table

This may be an obvious, or maybe it's a form of searching that isn't available?  There are ways in Quickbase to just get a summary of a table, grouping fields and counting the results to display in a report.  

Is there a way to search for a value in a field across all records of a table and then use that counted value within a formula or form rule?  If not the same table, what about with related table, BUT including records in the child table that are NOT related.

For example, find all records (non-related) in a table by searching a status field for the word "active" and returning that count to be used with another formula or within a form rule somehow?
Formulas and functions
Relationships
reports and charts
  • RyanAnderson2's avatar
    RyanAnderson2
    Qrew Cadet
    6 years ago
    Hoping there might be some thoughts on this question still.

    Through some more testing, I found that by doing the following, I kind of get the result I need, but it's extremely slow, so still looking for help.


    * create a new multitext field in which the Input Type is populated from a source field in the same table. 
    * the source (main) field is from the same table, allowing this new lookup field to now show all the values in that table for that source (main) field. 
    * I unchecked all the boxes under the advanced header, thinking it might help speed it up?
    * on the form, I created 2 dynamic rules.
    * rule 1: when record is saved and the main field has changed, change the lookup field to match this main field on the form
    * rule 2: when record is saved (after checking), see if the lookup field matches the main field. Also check if the main field is not empty (since empty ones are allowed). abort with a message that the value from the main field already exists in the lookup.


    This seems to work when I have the lookup field displayed on the form (which I don't want), yet it makes the form ice slow!!!  basically not functional for users who don't want to wait 1-2mins.  If I remove the lookup from the form, then the rules don't work it seems.

    Anyways, I'm messing with that still, but hoping someone might know of a way to handle the actual question.

    Thanks
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I'm not really understanding the ask here.  Do you need to be able to change the search term you are looking for?

    Or are you looking for the results to display as an embedded report on a parent record and you want children who match some word in a field on the parent record. 
    • RyanAnderson2's avatar
      RyanAnderson2
      Qrew Cadet
      6 years ago
      Specifically, I have a form with several fields on it for a table. One of those fields has a unique alphanumeric code we type in that should only be used "once" in a record.  Now the records already have the record id that is being used on all the parent-child table relationships.  

      We were going to just check the box called "Must be unique" for this alphanumeric field and call it a day, but the issue is that we also need to allow this field to be blank too. Not just for 1 record, but to allow many records to have this field be blank and pass.

      We couldn't figure out a way to do that ... have a field contain only unique value if it is entered in, yet allow blank values if they are not.   The only way we seemed to be going towards is a lookup against that field, but it makes the whole form sluggish and useless.

      We're trying to do this in a form because we want it so when a user decides to type in a value into this field, the form will block the save if that value already exists in another record as being used. If the user decides not to attach a value to this field and leaves it blank, then it will save.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      When you set la field is being unique, in fact blank values are allowed in a field marked as unique. In fact the only duplicates that are allowed in a field marked unique is if the field is blank.

      So why don�t you just mark unique and Call it a day.

      Except that what you would do would be to make a summary report and group the records by this unique field and sort them up on the number of records so that the duplicates float to the top. Then do a one time manual scrub of your data to get rid of the duplicates and you will be clean going forward.
    • RyanAnderson2's avatar
      RyanAnderson2
      Qrew Cadet
      6 years ago
      We'll look into that again today. We had a programmer check the unique field earlier and likely based off your comment, we should double-check that unique feature