Checking filters through record reference

In some of the “tools” we create, we have a variable way of setting up records which need to be “processed” by our tool.

For instance:

The tool needs to run for sales orders which are released.
For this, we have a header/detail setup:
The header has the reference to the table (table 36). In the details we add filters: Field 1 (document type) must be ORDER (filter value = 1). Field 120 (status) must be RELEASED (filter value = 1). Let’s go into some options to check these filters.

Let’s for the sake of this just assume a single function to check our information, which is passed the source record in some type, and the field ID / Field Filter Value to check.

Option 1: Compare the value to the filter value

The code

We check the value of the field versus the filtered value by getting the field reference.

You might notice that the input is a Record ID. In my setup, all tables are pushed to a queue, and processed afterwards by a NAS. The only way to store this in my queue is off course to save the RecordID.

Pro / Con

– Different data types
– Multi language
– Advanced filters (eg. date1..date2)
+ Nothing actually

Evaluation

This is a bad method. You should simple never check the value of a field directly to a possible value in my (humble) opinion.

Option 2: Set filters to compare

The code

Here, we set a filter on the primary key (SETRECFILTER), next we add a filter to the FieldRef and check if we still find the record.

Pro / Con

+- Depending on how you make your setup, you should be able to handle different data types
+- Depending on your setup, you should be able to handle multi language
(eg. filter on document type: 1|2 instead of Order|Quote)
+ Advanced filters will work
– Performance?

Evaluation

This is a far better method of checking the filters.You are able to check “advanced” filters, and do not directly check against a field value.

I saw a colleague of mine do something similar:
He set the filters on another RecordReference (all the filters). Next, he checked if his record was in the filtered set using the KeyReference of the primary key.
This method is also good, since it will not poll the database for each filter field. Only, when you have multiple filter levels on a single field, you could overwrite filters, and miss out on some.

Conclusion

By setting actual filters on a RecordRef, we are able to generically test if a record meets the requirements. This allows us to expand our tool to other tables, without the need to change code.

This is it right? Well…

After a while, one of our consultants told me that the queue was actually really big.
Let’s assume the first sample: table 36 with 2 filters.

In the queue, every single record was being added on table 36, all orders, all quotes, all invoices, …
this was due to using the Global Database Triggers. So their question was to check the filters sooner, before adding to the queue. This way the processing queue would become smaller and more insightful.

First thing I did was call my function out of the triggers, by passing the RecRef.RECORDID.
Well, you might feel this one coming? I started getting a lot of errors…

RecRef.GET(SourceRecordID); was failing on the insert triggers, since the record didn’t exist yet.. (off course).

Another problem, which you might not directly have seen coming: the filters were not checked in the correct way!

Let’s see this:
– Sales Order is set to status Released.
– Global Database Modify Trigger is fired, my function is called.
– RecRef.Get(SourceRecordID); => works good, it was a modify after all.
– We set the filters on the field Status, which needs to be released.
– EXIT(NOT RecRef.ISEMPTY); => returns FALSE!!!!

so, we released an order, which means the status becomes released, however, the record “did not meet the filters”. Why?

Simply, because on the modify triggers, neither INSERT nor MODIFY was already triggered. As a result, the RecRef.GET gets the unmodified version of the record which isn’t released yet.

this brings us to….

Option 3: Adding the data to a Temp Table and set filters

The code

In this sample, I open the 2nd RecordRef to the same table as my source, and as temporary (parameter 2 = TRUE).

I fill the field I want to check, with the actual value of my record and insert this into my Temporary RecordRef.

The next is almost the same, except that I no longer have my SETRECFILTER.

Pro / Con

– Not sure…
+ Performance => ISEMPTY should be happening in memory
+ Filters on the primary key shouldn’t interfere with the SETRECFILTER
+ All of the pluses of Option 2

Lessons Learned: Actual conclusion

We started with 2 options, wrote 3, but actually only (in my opinion) have 2 options.

After the review, the last option is the one we use now, next to having a generic way to apply filters, I have also learned some other stuff: Creating a temporary RecordRef. Didn’t know we could actually do it this easy.

There’s only 1 thing I’m missing, that is a transferfields on the RecordRef function.
Maybe a simple assign would work (RecRefTemp := RecRef), but I’m not sure this will not break the temporary flag.
I saw a function called DUPLICATE, but the description in the help made me even question the direct assignment more:

The RecordRef that is returned refers to a new record with the same filters, current keys, and marks as the original RecordRef. Any changes that you make to the filters, current keys, and marks of the new record are not observed in the original. This differs from assigning one RecordRef to another RecordRef. If you assign one RecordRef to another RecordRef, then both refer to the same record and changes that you make to one RecordRef are observed in the other RecordRef.

(…)

After the DUPLICATE function is executed, the RecordRef1 and RecordRef2 variables are identical.

I didn’t test this, so I’m not completely sure.

As for DUPLICATE, the description (last sentence above) also states that the variables are identical afterwards, so what about one being temp and the other not?

I guess the safest way, is to loop through the fields and copying the values one by one.
If anyone knows a better way, feel free to comment!

As for this post, I hope these methods have shown you some insight in generic filtering or maybe gave you some ideas for other cool functionality. In any case, I’d like to hear 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.