Hello, I am trying to do the following: I want a user to be able to “mark” certain records in a list. I created a table “marks” that carries the ID of the record, an integer field for the mark itself (a “1”) and the user name since several users have to be able to mark their own records.
To mark the records I have made the mark field (the “1”) a Check box without value list. Records are wonderfully added to the marks table, so there is no problem. The check without value list seems to be sort of a boolean.
The problem comes when searching. The user name resides in a global that is set at login time. When I search for the checks I get an error saying that column username is not defined.
Another problem is this: when changing the relationship from id to id and global.username to username to only id to id, even then, no records are found. When I use the field without the check (just a text field) and enter a “1” it works. It seems that the check without value list is sort of boolean only in “browse” mode…
Here’s a small demo solution that sort of does what you want.
just import this solution via repository.(it uses the default example_data
that ships with firebird)
notes:
open solution , go to customerslist and set user(pulldown) to a name.
This simulates logged in person.
check some records and then try setting the filter to “my selections”
only the checked records will appear.
(you could add an extra event script on the checkbox
that deletes a record in your “marks” table when going from checked to unchecked.
a) keeps your table small
b) don’t have to use a check column. (0,1)
I’m wondering if this technique from FM would work here or if the way mentioned above is faster:
In FM, I put the ID of the marked record into a global and the user can click “view marked records” which uses go to related based on a relationship between the global and the ID.
If the user wants to mark the entire found set, the script copies all records and appends the clipboard to the global.
would this work in Servoy? Sorry for the elementary question, but servoy supports multi-key relationships, right?
would this be faster since it’s storing the IDs in a global rather than creating a record in a table for each marked record?
Interesting stuff.
Since the foundset is not permanently stored in the global I thought of making a foundset table.
I guess Chris is considering to do the same.
Strange thing is that the stored foundset will not load (with a method) after closing down the app, it seems to be gone.
The field in which the foundset is stored however shows the foundset code.
I guess I am doing something wrong here , but what?
BTW: Regarding the FMP procedure I made a relation between two forms / tables.
The pk of form_1 is a global field.
This works only fine if one value is entered in the global (1-n).
(v.1.2rc11).
which are the IDs of 4 records in a table. When I made a method to go to the related records - forms.customerlist.controller.showRecords(g_marked_to_IDs) - I was expecting it to the show these 4 records but instead it showed none. So, please explain how to implement this technique.
it doesn’t matter that globals are cleared upon shutting down the app - marking records is just for a single session only. BUT THE PROBLEM is that when the user enters find mode, the global is cleared. This defeats the whole purpose. Can this be fixed? And you can’t copy the global to a field temporarily b/c the global may have thousands of IDs in it and that would exceed the field limitation.
I’m assuming based on prior posts that there really is no limit to the number of IDs in a global, right? So the found set could contain 50K records and putting each record’s ID in the global would be fine?
Lastly, to Ron’s point, I also really need to implement a technique that saves a found set. In FM I copied all IDs to a single field in another table and then used go to related from that field to retrieve the found set. with an SQL backend there seems to be a field limitation that makes this technique impossible. I’ve been told I actually have to save a single record for each record I’m trying to save, and I’m worried this will take way too long. Desparately searching for a way around these problems and would love to share what we come up with…
Resuming: we have the same problems with issue 1 and 4.
Ad. 4) As I told, I succeed in storing the foundset in a textfield.
I assume this will work with different foundsets with different records as well.
Problem is that the foundset is not stored, although it is not a global and I use the store Data command.
point 1: How did you put those id’s in the global?
Because currently you can only do showRecords(XXX)
where XXX is a foundset or a jsdataset (you get back from the Database Manager)
point 2: how are the globals cleared? I just tested it and the global is not cleared.
point 3: For the global it doesn’t matter but i think (depending on database) is that the select you want again (with showRecords(XXX)) is a IN select with all those PK’s and that in select is not unlimitted.
point 4: We have first to look how you store that value in a record (like id with a \n as seperator and then get that value back as an array of id’s) and then you could do showRecords(with_an_array). We will look if we can build such a thing.
Very happy this is getting addressed as “marking records” and “saving a found set” (2 DIFFERENT issues) are two top priorities for all of my clients who I’m considering switching to Servoy. Lots of issues. Apologize for the obnoxiously long post, I just want to bring these loose ends together.
First I’ll state the need, then the questions it brings up:
WE HAVE 2 NEEDS:
MARKING RECORDS - To allow each user to mark records individually or the entire found set at once, very quickly. Typically, users will perform a search, view the results as a list, mark a few of them, perform another search, mark a few, etc. This feature is used daily by my clients.
-Ideally there should be no limit to the number of records that can be marked.
-If the user wants to quickly mark all records in the foundset, she just clicks “mark found” and quickly all records are added. Sometimes this is just 50, other times it’s 15,000 records.
-And after the user is satisfied with what’s been marked, s/he can click “view marked records” and Servoy will load a found set of these marked records.
-It goes without saying that each user should be able to maintain their own set of marked records. And when the solution is closed, it’s fine for the marked set to be cleared.
SAVING A FOUND SET or CREATE RELATED RECORDS - A similar but separate need is to be able to quickly save a found set or create a related record for each record in the found set.
Example A: Say an invitation goes out to 15,000 people. Users need to know who received the invitation, what the title of the invitation was, and a file reference to the actual invitation on their network drive. In FM I use copy all records and copy the IDs into a SINGLE global field. Then a new record is created in a table called “Mailings” and the contents of the global is put into a single text field in this new record. The user also gets to name the mailing and store a file reference to the letter that was sent. This is extremely fast. For large found sets for which the 62K limitation would be exceeded I have a utility that splits up the set and might create 3 or 4 records instead. After this is done users can view in the contact’s “mailings” portal all mailings the person has ever received. And they can then retrieve this found set b/c a go to related in FM goes to each record whose ID is in the field (separated by carriage return) on the left side of the relationship.
Example B: A user pulls up a found set of people in class X. There are 800. Marks all in the found set. Unmarks just those students who didn’t attend. clicks “view marked.” Now found set is 785. Clicks “create attendance record for found set” which quickly creates a related record in the attendance table for each of the 785 people who attended. Then user goes on to next class. In FM I would import from the class_registration table into the attendance table and have it create a record with just the ID of the person. For even 2000 records in Filemaker, this is just a few seconds. I just tested in Servoy using a method that loops through and creates related records - and it took about 1 minute for 1000 records.
-it’s not enough to “save the criteria” because there is no common criteria.
-Users must be able to very quickly “save the found set” / “create related records” and in testing with Servoy, creating a few thousand individual records using the while statement and create new record, as has been suggested, just takes too long.
QUESTIONS
MARKING - For marking records and for so many other features, it would really be helpful to have a way to store in memory (so it’s not a disk action and can happen very quickly) a virtually unlimited number of IDs and to have the loadRecords or showRecords function be able to create a found set based on those IDs. I think Ron and I are both confused about how to do this. the pkdataset feature only works with 200 records, the duplicateFoundSet is for a different purpose, etc.
AN IDEA - Without knowing much about the constraints the team is working with (trying to get there guys…thanks for your patience) what seems like the simplest approach is to be able to use the “copy all records” command to copy the IDs into a global (I understand Servoy globals are limited only by diskspace or memory) and to have some feature that allows you to go to related from that global to each record whose ID is stored in the global. Or maybe there’s some neat “load found set from an array” feature. Either way, I AM SURE I’d USE THIS FEATURE MANY TIMES. It just makes life so much easier for users.
I’m more stuck on how to save a found set / create related records. But here’s an idea: If the only solution is to save a single record in another table for each member of the found set (because of SQL databases’ field limitations), then would it be possible to create a function that will execute a series of UPDATE commands on the backend database WITHOUT TIEING UP SERVOY so the user can go about his business while the 20,000 records or so are being created? I’ve noticed several other posts noting that it takes a while sometimes to create related records. I bet this would be a really great feature if it were possible. I think there is something like this in PDM’s SQL plug-in for Filemaker that works great. If something like this were possible I’m also sure I’D USE IT MANY TIMES as all of my solutions are constantly creating related records.
It’s really a pleasure to have a line to the team and to know you’re paying attention. thank you.
I have build support now that you can assign FoundSets or JSDataSets to a dataprovider. (that has to be a text type) It will convert such a dataset to an id list. After that you can convert that list back to a jsdataset with a new method..
something like this (still can change a lot!)
subject = controller.duplicateFoundSet();
(subject is a text dataprovider)
an then back:
var p = databaseManager.convertToDataSet(subject);
forms.actions2.controller.showRecords(p);
but this can’t be done for 15000 records… that will not work (database dependend where the limit is)
This will not filter the doubles!!
so if globals.mark already contains id 1 and the current foundset also has an id 1 it wil have 2 id 1’s in the foundset (one for example on position 2 and other at 25)
jcompagner:
but this can’t be done for 15000 records… that will not work (database dependend where the limit is)
This seems to be getting to my Need #1 / Example B, above: Marking records (NOT saving found sets - that is a different matter).
When a user “marks” a record, its ID is placed in a global. When a user marks the entire found set, all of the found set’s IDs are placed in the global. We should be able to load the found set by loading the IDs from that global.
If it’s true that Servoy globals have no practical character limit than we should indeed be able to mark > 15,000 records.
you can mark (the foundset in a global) and store (put the foundset in a record dataprovider) the global can be as big as youre memory can hold so that is not a problem. The biggest problem is that you can store 15.000 id’s in a globa. But you wil not be able to get a working foundset for that because of the query we have to generate for that can’t handly that many input values.