BizTechBlog Logo
Wednesday, October 31, 2018

How to filter data in SyteLine and CloudSuite Industrial

Learning to use “Filter in Place” (F4) is one of the most important skills to master to effectively and efficiently use CSI-Syteline.

...But “Filter in Place” is not the only way to filter a collection. “Filter by Query” or the query form (i.e. “Items Query”) offers more sophistication and ease of use than “Filter in Place.” Additionally, these filters can be saved and used again and again without having to build the entire syntax over.

Finding Records with Filter-in-Place

When you activate Filter-in-Place, any collection currently associated with the form is cleared and fields on the form become blank. You can then specify filter criteria in one or more fields and retrieve a collection of records that meet those criteria.

If a previous search has already been done, any fields that were used as search criteria can still have the search criteria in them. To clear the form of all search criteria, select Actions menu > Filter > Clear in Place or F5.

To find records with Filter-in-Place:

  1. Open a form that is used to retrieve records.
  2. Activate Filter-in-Place mode. Select Actions > Filter > Begin in Place or F4.
  3. Specify a value in one or more fields to specify criteria for the records you want to retrieve.
  4. Perform Filter-in-Place. Select Actions > Filter > Execute in Place.

The system displays all available records that match the filter criteria you designated. The number of records retrieved is limited to the current cap on data records.

  1. Optionally, to view the next set of records (beyond the record cap), click Get more rows in the current collection
    • Or click this icon on the toolbar:  csi-filter

If no records meet the criteria, the system automatically places the form in "new" mode. You can create a new record as you would after selecting Actions > New (or Ctrl + N), or you can repeat this procedure with different filter criteria. If you repeat the procedure, your previous filter criteria are initially specified in the fields. You can adjust them, or you can remove all of them. To remove all of them, select Actions > Filter > Clear in Place or F5.

Here's an example:

  •  Filtering for items that have the word “Seat” in the description
  • Source = Purchased
  • Quantity On Hand = >0

csi-filterinplace

After pressing F4, the system returned 5 records that matched my filter criteria:

csi-filterinplace2

Finding Records Using a Query Form

Most multiview forms have a query form associated with them. You can use these query forms to select a collection of records and return the collection to the multiview form.

Caution: Records retrieved by a query form replace the current collection of records in the multiview form. Be sure to save any changes you want to retain in the multiview form before you retrieve records with a query form.

To perform a typical query using a query form and return the results to the associated multiview form:

  1. With a multiview form open, perform one of these actions:
    • Press CTRL+Q.
    • Select Actions > Filter > By Query.
    • Right-click on the form somewhere other than on a field, and then select Filter > By Query
    • Note: On a form that is not associated with a query form, the By Query menu option is not available.
  1. Optionally, use one or both fields on the Primary Criteria tab to set the primary search criteria.
  2. Optionally, use the fields and buttons on the Additional Criteria tab to refine the search criteria further.
  3. Click Refresh. The system returns all records that meet your search criteria.
  4. Optionally, repeat Steps 2 - 4 as desired to get the return results you need.
  5. To return the results of your query to the parent multiview form, click OK.
  6. When asked if you want to return the result to the mutliview form, click Yes.

 

Filter Options (for either Filter in Place or Filter by Query)

If you leave all fields blank, the collection is unfiltered, and the system retrieves all records up to the current cap on data records. This is often undesirable, so the system provides several means to filter and thus limit the return results for a search:

  • You can use wildcards in filter criteria as you would in a query form. The default wildcard is *
  • You can use operators such as >(greater than), < (less than), and <> (not equal to) to augment your filter criteria.
  • Note:  Unlike query forms, Filter-in-Place does not support the logical operators AND and OR. If you need to specify multiple criteria for one field or perform Boolean searches, use the Additional Criteria tab of the query form associated with your form, instead of Filter-in-Place. Also, validators do not run in Filter-In-Place mode.
  • To find records that have blank values in specific fields, use the null
  • To retrieve filtered criteria values in certain drop-down list fields, use the Find command on the right-click menu.
  • To refine your search, you can use as many fields as necessary.

For example, you could: use all of these options in one search:

  • Specify41* in a Postal Code field to retrieve all customers in a particular set of zip codes.
  • Specify <>nullin a Past due field to limit the search to customers who are past due on their payments.
  • Specify >4999in a Past due amount field to further limit the search to customers who are more than $5000 in arrears.

The results of this search would return the records of all customers who have a postal code beginning with the numbers 41, and who are $5000 or more past due on their accounts.

 

You could also filter your data by exporting records from a form to a CSV-Excel compatible file:

  • This only exports the records in the current collection
    • Click the Export to Excel icon on the toolbar
    • Actions menu -> To Excel
  • For options to export all records, specify filename, file type and directory
    • Form menu –> Export To File…
  • A form may be personalized to filter on opening
    • Example: for a customer service associate responsible for a certain User Type, or a planner that only needs to review certain Item Masters.
  • Drop-down lists can be filtered for certain characteristics, sorted in different sequences or have additional data displayed – without changing the underlying source code.
    • Example: “The Customer number and name may not be enough unless I know where the Ship To (or Vendor, etc.) is …?”

 

 This CloudSuite Industrial / SyteLine tip was brought to you by BizTech ERP Consultant, Rod Lindstrom

    

Follow BizTech!

Subscribe to Email Updates

 
 
 
 
Schedule Your Free Consultation

Popular Posts