working with a large found set

[patrick]

Hello, I want to offer my users to put certain records in a "group". so there is a table called 'groups' that carries the pk of the relevant records plus a group id (that links to a a name). the only way I found to do this is to "walk" through the records in a loop an create the group entries.

here comes question 1: could you think of an import feature that does the job for me (that writes certain fields of the found set in another table). that would be extremely helpful for several issues that i have to solve...

question 2: Servoy loads 200 records when the found set is larger than that. everything is fine when you scroll through a large set, since more and more records are loaded the further you go. but when you walk through your records in a loop, the loop exits at record 200. the problem is that I am using the maxRecordIndex to do that. and that gives me 200. a full table count wouldn't help, since I don't know the last search request (I don't know what to count). How do I find out, how large the FOUND set really is?

Did I make myself understandable? Any ideas anyone?

Thanks.

[jcompagner]

<2>
How do you loop?

if you loop like this:

for(i=1;i<=controller.maxRecordIndex;i++)
{
}

it should go OK because maxRecordIndex will update itself

[chris]

Hello,

Patrick asked:

"1: could you think of an import feature that does the job for me (that writes certain fields of the found set in another table). that would be extremely helpful for several issues that i have to solve... "

1 - I use this technique in FMP extensively. If I redevelop in Servoy I'll need some way to quickly write certain fields, usually the primary keys, of the found set of one table into another table. This is for saving groups or even saving a record of a letter sent to all people in the found set.
QUESTION - is the best way to loop through and create records? How long do you think that would take using Firebird with a found set of 5000 records?

2 - I also use FMP's importing (with matching set) to view all records in 1 table that are related to all records in the found set in another table. For example, I'm viewing a found set of 500 people and I want to view all checks they've ever written to the organization. How would I do this in Servoy?

Kind regards,
Chris

[Jan Aleman]

Regarding Q1:
Why don't you just save the criteria of the foundset itself?
Q2:
If you want the real foundset count use:
databaseManager.getFoundSetCount(foundset);

[chris]

Regarding Q1:
Why don't you just save the criteria of the foundset itself?

The purpose is to allow users to save lists of thousands of people who have received an invitation to an event. Users put the list (the found set) together by carefully viewing a long list of records and marking just the ones wanted and then executing a "go to marked" method to make the found set just include those records that they marked. SO THERE IS NO COMMON CRITERIA to save.

Users will want to look in a portal to see "all invitations" a particular person has received.

So, what do you think would be the best thing to do so each person who received the invitation shows up in the portal?


Q2:
If you want the real foundset count use:
databaseManager.getFoundSetCount(foundset);[/quote]

I've heard this is "expensive." I like to have each main screen in each module show the total records in the database - like the total people they have in their system for example. How would one accomplish this?

Thank you.

[chris]

2 - I also use FMP's importing (with matching set) to view all records in 1 table that are related to all records in the found set in another table. For example, I'm viewing a found set of 500 people and I want to view all checks they've ever written to the organization. How would I do this in Servoy?

[Harjo]

So, what do you think would be the best thing to do so each person who received the invitation shows up in the portal?

The only way to accomplish this, is to make loop, thru the foundset and make a new record in a related file:
(Maarten has helped me with this one)

for example:
table: relation (Your customers)
table: lineitems (a portal to make records for every mailing that is send)
table: mailing (this is the table where you create your mailing)

relation between relation and lineitems: relationid = relationid
relation between mailing and lineitems: mailingid = mailingid

First of all it loops thru your foundset and makes an array of all your relationid's
after that it loops again, to make for every relation a new entry in the lineitems

so start in mailing and put the currentrecord (your current mailing) in a global: mailingid. Than go back to your relation-table and start the following code::

Code: Select all

//to convert relationid of your foundset to an array
var relationArray = new Array()
for ( var i = 1 ; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.recordIndex = i;
relationArray[i] = relationid;
}

//than loop again with your array to make records in lineitems.
for( var i = 1 ; i <= relationArray.length ; i++ )
{
  forms.relation_lineitems.controller.newRecord()
  forms.relation_lineitems.relatienr = relatiesArray[i];
  forms.relation_lineitems.mailingenid = globals.mailingid
}


Hope this helps

[Guest]

Very helpful! thanks for sharing the code.

Do you know what the practical limit is on the number of items in an array or in a variable? Say the found set is 25000 records and the array therefore contains 25000 IDs of 8 characters each. Would this be a problem?

Thanks again.

[maarten]

The maximum length(nr of elements) allowed for an array is 4,294,967,295.
Memory is only limited by your hardware.


BTW: HJK was fiddling some more with the loop, and it turns out that next code (shorter) works fast as well:

Code: Select all

//script is attached to main form relations
for( var i = 1 ; i <= controller.getMaxRecordIndex()  ; i++ )
{
  controller.recordIndex = i
  forms.relation_lineitems.controller.newRecord()
  forms.relation_lineitems.relatienr = relatienr;
  forms.relation_lineitems.mailingenid = globals.mailingid
}

Thanks Harjo!

[chris]

Thanks again! First, tangentially, is the max array length really that strange unround number? Just a property of JS? Just curious

I had heard the getMaxRecordIndex is "expensive." Does that mean it will take a long time to return the value? If so, can you estimate how long it would take if the total recs in a table is >50,000?

also, in FM my users always want to know how many records they have total in the db (e.g., how many people or orders or whatever they have total) so I put status(currentrecordcount) as an unstored calc field at the top of every screen. Any way to replicate this?

Thanks.

[Jan Aleman]

Thanks again! First, tangentially, is the max array length really that strange unround number? Just a property of JS? Just curious

Try adding 1 and then devide it by 2 and again by 2 repeat that about 32 times. Somehow computers like the number 2...

I had heard the getMaxRecordIndex is "expensive." Does that mean it will take a long time to return the value? If so, can you estimate how long it would take if the total recs in a table is >50,000?

getMaxRecordIndex is not expensive as it retrieves the max count for the current set. Speed varies depends on many factors, the best is to give it a try! Shouldnt cost you more than 10 minutes to test it.

also, in FM my users always want to know how many records they have total in the db (e.g., how many people or orders or whatever they have total) so I put status(currentrecordcount) as an unstored calc field at the top of every screen. Any way to replicate this?

You can use the count function on the database manager to get the real count. On a sql database that count will be executed every time you retrieve it and depending on your database size and available hardware that can be an expensive operation. There are very little SQL frontends that show continously how large your foundset is, apart from counting data I don't see much benefit in displaying it continuosly, can you give some examples why it is useful?

[chris]

On displaying a status bar with:

record number | founcount | total record count

This is just a standard interface feature that I'm used to. Here it is, below. As you can see, it helps users who are learning the system understand what a "found set" is versus "all records" and it gives people security that they are working with a particular found set rather than all records, or vice versa.

At the very least, I don't think users can do without the constant knowledge of the found count. I noticed Servoy doesn't display the exact number past 200 and my users are usually dealing with lists longer than 200.

What are your thoughts? Much appreciated.

[Jan Aleman]

You can use

databaseManager.getTableCount(foundset);

for that. If the count is like in your example that won't be a problem. However if you have a 100 million row table and you are find 20 million rows that count can perform a significant load on your system.

[bcusick]

On displaying a status bar with:

record number | founcount | total record count

This is just a standard interface feature that I'm used to.

Hi Chris,

I can understand what you're saying. However, you must remember that desktop database programs with a proprietary database backend can do all kinds of stuff (like always showing the number records, copying 15,000 records into a global, etc).

One of the "problems" I had when first using Servoy to build real solutions - was to try to forget all the FileMaker "work-around tricks" that I had to use - and to expand my paradigm to think about the best solution to approaching user interface solutions - not just what I was used to.

At the very least, I don't think users can do without the constant knowledge of the found count. I noticed Servoy doesn't display the exact number past 200 and my users are usually dealing with lists longer than 200.

I know, people hate change - especially users. You CAN replicate what you have done in FileMaker inside of Servoy - but it will be slower, that's all.

REMEMBER RULE #1: Use the right tool for the job. If this is "critical" to you or your users - perhaps the right thing is to continue to use FileMaker.

Cheers,

Bob Cusick