omit via loop

[jim]

I am trying to loop through a found set and omit certain records (in this case, people who say "no mail, please" by checking "yes" on the con_no_usps field). What happens is that some records where con_no_usps is marked "yes" are not omitted the first time around, but may be omitted if the method is run again. I have looked for a pattern for why those records are not omitted the first time around, but cannot find it. Here is the relevant part of the method:

Code: Select all

for ( var i = 0 ; i < curFoundSet ; i++ )
   {
   if (forms[curForm].con_no_usps == 'yes')
      {
      forms[curForm].controller.omitRecord()
      }
   else
      {
      forms[curForm].controller.setSelectedIndex(i +1)
      }
   }

For what it is worth, this is how it is done in FileMaker:

Code: Select all

Loop
    If[contacts:con_no_usps = "yes"]
        Omit Record
    Else
        Go to Record/Request/Page [Next; Exit after last]
    End If
End Loop

Any pointers would be appreciated!

Thanks,

Jim

[bcusick]

Why don't you just do a find on the current found set? You can expand/contract foundsets - that's way easier.

The problem with a loop is that your variable i is getting incorrect and skipping records when you omit.

[jim]

When I do this:

Code: Select all

controller.find()
con_no_usps = 'yes'
controller.search(false,true)

I find the items I want to omit from the current found set.

But when I use '!=' instead:

Code: Select all

controller.find()
con_no_usps != 'yes'
controller.search(false,true)

the items are not omitted.

So I try this:

Code: Select all

controller.find()
con_no_usps = 'yes'
controller.search(false,true)
controller.invertRecords()

and this

Code: Select all

controller.find()
con_no_usps = 'yes'
controller.search(false,true)
controller.loadOmittedRecords()

but neither did the trick.

There appears to be something I am missing; please enlighten me.

Thank you,

Jim

[DomTom]

Hello Jim,

When I do this:
Code:
controller.find()
con_no_usps = 'yes'
controller.search(false,true)
I find the items I want to omit from the current found set.

But when I use '!=' instead:
Code:
controller.find()
con_no_usps != 'yes'
controller.search(false,true)
the items are not omitted.


I see 2 different ways to do :

1. you can invert records after that you have find the records you want omit.

or

2. don't use : con_no_usps != 'yes' but con_no_usps = '!yes'


Hope this can help.

DomTom.

[jim]

I have tried both of those, and neither work for me. But, I have figured out why: the con_no_usps field is formatted as a checkbox; the values are either "yes" or "". Neither (!) nor invert work for searching values of "". This works, though:

Code: Select all

controller.find()
con_no_usps = '^'
controller.search(false,true)

Turns out I needed the hat!

"And he wore a hat, and he had a job
And he brought home the bacon so that
No one knew..."
(Devo)

Thanks Bob, and DomTom.

[ngervasi]

I have tried both of those, and neither work for me. But, I have figured out why: the con_no_usps field is formatted as a checkbox; the values are either "yes" or "". Neither (!) nor invert work for searching values of "". This works, though:

Code: Select all

controller.find()
con_no_usps = '^'
controller.search(false,true)

Turns out I needed the hat!

There's a warning tough.
I think that if you check your field and then uncheck it Servoy will not save the NULL value again in the column but it will save an empty value "" (I'm not sure but it's worth to check it out).
Anyway if you want to find null AND empty values you can use this:

Code: Select all

con_no_usps = '^='

[jim]

I did test it, and it seems that "^" will find empty checkboxes whether or not they have been previously checked. "=" returns nothing.

Thanks for the warning, though, Nicola. I have changed it to "^=" anyway, just in case.

Jim

[faheemhameed]

will this work?

Code: Select all

var frmObj = forms[curForm];

var totRecords = databaseManager.getFoundSetCount(frmObj.foundset);
var omittedCount = 0;
var counter = 1;

frmObj.controller.setSelectedIndex(1);
while (counter + omittedCount <= totRecords + 1) {
   if (frmObj.con_no_usps == 'yes') {
      frmObj.controller.omitRecord();
      omittedCount++;
   }
   else {
      frmObj.controller.setSelectedIndex((counter + 1) - omittedCount);
   }
   counter++;
}

plugins.dialogs.showInfoDialog("", "done");



[John]

I've found that omitRecord() maintains the current sort but not the current order when the foundset is sorted on a column that contains non-unique values. (Version: 4.1.3 - build 672) When omitRecord() reorders the foundset, it allows the for-loop to skip some records. Which records get skipped depends on which sort is the current sort.

Does anybody know why that is? I've fixed it by sorting on the unique identifier before the for-loop and then resorting with the original sort after the for-loop. But it seems wasteful to sort twice for each for-loop plus once for each omitRecord(). Would it be possible for omitRecord() to be changed to maintain the current order instead of the current sort?

John

[kazar]

Without knowing how to solve the problem as you describe it, I'm curious as to whether it could be reframed so that you use a different approach. You have a set of records that was selected somehow. You wish to create a subset of those records by omitting rows that match some criteria. Instead of looping through these records one by one running some sort of test to determine whether each row will be kept or discarded, might it work instead to run a query with a NOT argument?

[jcompagner]

dont know exactly what your problem is or why you have it but your code is not really save or good practice

first dont loop like that through the user interface
controller.setSelectedIndex should only be used if you really want to select 1 record in the ui that a user then sees as the selected record
dont use it for logic loops in your code

also if you want to ommit stuff like that do it by looping from the end to the beginning...
so that ommit doesnt really interfere with your loop

Code: Select all

for(var i=maxRecordIndex; --i>0;)
{
   var record = foundset.getRecord(i);
   if (record.xxx = yyyy)
    {
           foundsert.ommitRecord(i);     
   }
}


[John]

Thank you for your tips on using omitRecord() inside a loop.

The problem is that omitRecord() changes the order of the foundset. So inside the loop, accessing records by the index will cause some records to be accessed more than once and others never. This problem only happens when the foundset is currently sorted on a column that contains non-unique values. During omitRecord() the foundset is sorted using the current sort but the current order is not preserved.

What do you think is the best way to solve it? Should I sort the foundset on the unique identifier before the loop and then resort with the original sort after the loop? Or is there a way to configure omitRecord() to preserve the order of the foundset?

Thanks again.

John

[jcompagner]

omitRecord doesnt really change the order.

The only thing is that you bounce against that if you hit record 200 then it will load the next 200 pks.
And i guess that pk query does mess something up for you

But try what i say
start from the last record.. then the pk query is done already for everything and everything is loaded.

[John]

Johan,

I forgot to mention that we already use your method for iterating backwards through a foundset.

But the problem with using omitRecord() inside a loop can be seen without using a loop. What I did was iterate through the foundset to see what order it was in, make a single call to omitRecord(), and reiterate through the foundset to see that it was now in a different order, albeit in an order that still fits the same sort criteria.

Thanks for your help.

John

[jcompagner]

if you have a sample where you see that the foundset does a requiry (the only way that the ordering of records could be changed..) if you do omitRecord() on the foundset then please make a case for this.