Populate Checkboxes from Selections in Another Library

[Aaron Dahl]

Good evening.

I am working on a meeting minutes library tied into existing libraries. We need to populate a checkboxes field based upon the selections within another table. I'm trying to abbreviate the functionality to demonstrate what we're trying to achieve.

Library 1 "People" \ Names (text field), other information...

Library 2 "Projects" \ Project Name (text), Project Type (single-choice), other information..

Library 3 "Teams" \ Project Name (link to Projects library), Involved People (link to People library), Role (text)

Library 4 "Meetings" \ Date (datetime), Project Name (link to Projects library), Attendees (checkboxes), Discussion (long text), other information...

The scenario is that within the Meetings form, I wish to only present the user with the names of the Involved People (Teams library).

For example:

People library include Mary, Mark, Luke, Amanda, Jane, Ben, Tim and Shawn

Teams library for project "Bogus Highrise" only includes Mary, Mark, Amanda, Luke and Shawn

When a user goes into Meetings to create a new meeting for Bogus Highrise, the Attendees (checkboxes) should only display Mary, Mark, Amanda, Luke and Shawn ( it shouldn't show Jane, Ben or Tim)

I could set up a field within Projects library called Involved People (link to People library). It's easier to generate a project directory with the way I have it set up, but if this functionality is made simpler by looking up a CSV field in another library, I can tweak some things to make this work. I think this should be some sort of a trigger, but I don't know javascript much and I can't seem to get a basic sample functioning.

Any pointers would be greatly appreciated. Thanks,

[Bill Crews]

This seems like one of those applications where the links guide the flow, so if Attendees could be a many-to-many link to Teams, once your team is checked the team members are laid out in front of you. However, if not all team members attend every meeting and some meetings have attendees from outside the team, I see where it could get pretty complicated. But if that happens a lot, it would seem like you'd want to show all people in the first place.

My question is... What do you want the script to do? If the solution I proposed above would work (only team members) and if Attendees links to Teams, then the main question is how you want people to select the attendees. If from a list of team members, just use a link field and use multiselect to choose the attendees. If you really want to use checkboxes, you might be able to do it, but it wouldn't be pretty. You'd have to save as a template with data and then use a script to edit the template to create the Checkboxes field with the right team members. The template file format is not documented, so it could change underneath you at any time, breaking any scripts that would be dependent on it. Not advised.

I'd say to use a many-to-many link to entry field to identify team members and then provide a list of teams for the meeting and get used to multiselecting from a list. And maybe it's not so bad to see the whole list of people. Sometimes they might join your meeting.

[Aaron Dahl]

Thanks for your quick response Bill.

Ideally, I would like to see checkboxes of Involved People, but the many-to-many relationship will work.

My problem is it needs to filter the list of names in the Teams library to show only those where the Project Name (within Teams library) = the Project Name of the Meeting. (the Teams library includes teams for all projects). Generally the user should be creating a Meeting entry from within the Project Entry, the Project Name field is passed from the Projects library to the Meeting entry. I would like MDB to compare this Project Name value against the Project Name in the Teams library and present the user with only the Involved People for that particular project.

Right now it brings up the entire list of entries.

Thank you,

[Bill Crews]

I may not have been clear enough before. In terms of scripting -- JavaScript fields, triggers, actions, custom data sources -- to show checkboxes in Memento, you need a Checkboxes field to be defined in the library, and the items to be displayed are part of that, but scripts have no way to define a field and its items. To do that, you'd have to do something exotic and beyond what Memento normally can do, such as the thing with the template that I mentioned previously, since a template can define a Checkboxes field. But a script editing a template is not supported or advised.

So, how else does a Checkboxes field with the right items for your team get defined? You'd have to edit it every time team members change. If you had that, you could probably get around which checkbox field to show by using field dependencies, if that was a problem. But keeping the Checkboxes field in sync with the Teams library would be error prone and would involve a lot of work when people change teams, so that's not advised.

Now, there are other ways to allow a user to select among multiple choices. The multiple-choice list field is very similar to checkboxes, except that you can choose to allow items to be created if they're not already there, but I know of no way to use that feature to accomplish what you want, so it's effectively the same as the Checkboxes field.

That leaves us with displaying a list of entries and allowing the user to multiselect among them. In the user interface, once the team is selected (however you get to that from whatever library you start with), the team members list should show, and the user can multiselect.

But who knows? Maybe someone else can think of another way to do it. Let's see if anyone posts something like that.

[Aaron Dahl]

As a start I've set up a Trigger within Meetings library that populates the multi-choice list using the following:

entry().set("Attendees","Martin,Julia,Amanda,Frank");

Without otherwise defining the items in the multi-choice list, this populates the list successfully. Now, in front of setting the entry items, I need to clear what is in the current list. I have attempted each of the following:

entry().set("Attendees",null);

entry().set("Attendees",[]);

entry().set("Attendees","");

None of these appear to clear the list of items. What other method can be called to clear the list of items prior to each new entry?

Additionally, then what I'd like to do is run a find(query) to pull the values of Involved People from the Teams library where the Project Name (in Teams) matches the Project Name (in Meetings). Once the array of values is collected from that Teams library, I need to pass it to the entry().set("Attendees", {replacing Martin,Julia,Amanda,Frank} )

I'm really a javascript novice here, so I'm having quite a bit of trouble getting this to work correctly.. I'm trying to use this post as a basis: https://groups.google.com/forum/#!searchin/mementodatabase/var$20ricambi%7Csort:date/mementodatabase/gK1BTlOBW5o/PELRrmA6BAAJ

var teams = libByName("Teams");

if (teams == null) {
     exit (); }

var meeting = entry ();    //not following this part
if (meeting == null) {
     exit (); }

else {
     var optattendees = teams.find (entry().field ("Involved People"));
    }

    for (i = 0; i <optattendees.length; i ++)
       {
       if(optattendees[i].field("Project Name") == meeting.field("Project Name"))
       {
        optattendees[i].set("Attendees","ordinato");    //I don't know what I'm doing here to call the array I am trying to define above.
       } //
}

Any pointers for either of these would be great. I will continue to look for ways to write an array from another library and post as I uncover workable methods.

Thanks,

On Monday, August 20, 2018 at 9:52:06 PM UTC-5, Aaron Dahl wrote:

[Bill Crews]

entry().set("Attendees","Martin,Julia,Amanda,Frank");

It's true that this will add these items in a multiple-choice list field that has the "Allow creation of items while an entry is being edited" box checked (not in other multiple-choice list fields or a checkboxes field).

entry().set("Attendees",null);

entry().set("Attendees",[]);

entry().set("Attendees","");

None of these appear to clear the list of items. What other method can be called to clear the list of items prior to each new entry?

I know of no way to subtract items from the multiple-choice list field in the user interface, so I'd be surprised if there was a way using set().

Any pointers for either of these would be great. I will continue to look for ways to write an array from another library and post as I uncover workable methods.

Good luck, as you make your attempts! Often, where there is a will, there may be a way that others haven't thought of or tried. I'm doubtful you'll have that luck, but I'll be interested to read about it, if you do.

[Aaron Dahl]

Good evening.

For now I'm just working on a script to populate the multi-select list. I've got this initial take at what the trigger needs to do to use the project name in the current entry, and select only the Individuals in the Teams library and add those people to the list of available Attendees.

I'm getting an error message that "Individual" is not defined but I thought I'm using the field name correctly. Is it because Individual is the link field?

var teams = libByName("Teams");

var e = entry();
if (e.field("Project Name") == null){
exit(); }
else{
var people = teams.find(entry().field("Individual")); //get names of individuals in Teams library
}
for (i = 0; i < people.length; i ++) //cycle thru entries to get all people
{
if(people[i].field("Project Name") == e.field("Project Name")) //select only individuals assigned to project of current record
{
e.set("Attendees", people.join()); //populate multi-select with the matching names
}
}
message(e)

Do the steps above appear logical? What an I missing?

Thanks,

[Bill Crews]

e.set("Attendees", people.join()); //populate multi-select with the matching names 

Well, people is an array of entries, and join() is a method for arrays that returns the string representation of each one, separated by commas. Normally, the string representation of an object is some combination of the values of all the properties of the object. In the case of an Entry object, maybe that's the values of all the fields. But again, I haven't tried it in Memento, so maybe it's just the entry name.

But the Entry object's set() method wants (in the case of a checkboxes field) an array of strings, not a comma-separated string of strings. So, maybe loop through people and do a  attendees.push(people[x].field("Name")) on each one that you think should be in the meeting; then do the set... people.set("Attendees", attendees).

[Aaron Dahl]

Thanks Bill.

I will try the loop and push functions to get the field to populate.

On Monday, August 20, 2018 at 9:52:06 PM UTC-5, Aaron Dahl wrote:

[Aaron Dahl]

I have changed how we handle the project Teams. I have gotten rid of Library 3 "Teams" and now include a field in Library 2 "Projects" called "Personnel". Within this field, we will simply list the individuals involved on the project and their respective roles with regards to the project.

i.e.

Bob, Builder

Jane, Designer

Frank, Engineer

Buck, Inspector

This is a short example, but some complicated projects will have 50-100 involved people.

So now, back to the Meetings library. I've got two fields which need to be populated using the names (only the names, not their roles) from the Projects library "Participants" (which I want all people unchecked by default) and "Distribution" (which I want all people checked by default. User can change as necessary.

This is what I have so far:

var e = entry();

var involvedPeople = e.field("Project")[0].field("Personnel").concat("\n");

var names = involvedPeople.replace(/,.*\n/g, ",");

e.set("Distribution",names);

e.set("Participants",names);

entryDefault().set("Participants",null);

It's actually working on the desktop Memento, but for some reason it is not working on the mobile version. I receive an error "Cannot call method of "field" of null (Tigger 4.js#2)". I don't understand this because I am initiating the Meeting from within the Project Entry of the Projects library, so the Project Name is being passed immediately to the new Meeting. I'm also not getting the Participants field to populate with all the names and then return them all unchecked. I've tried null and "".

Incidentally, I've tried the Trigger Examples - specifically the "Copying the value of Checkboxes or Multiple-Choice field to another library" (one with My Today's Activities and Activity Plans) and this example is still a bit over my head. I would love to see another example of a Trigger that pulls data in from another library.

I will get this figured out eventually. Thanks for your help.

Regards,

On Monday, August 20, 2018 at 9:52:06 PM UTC-5, Aaron Dahl wrote:

[Aaron Dahl]

I've now simplified the structure and include the link to the People library within the Projects (called "Involved People").

Library 1 "People" \ Names (text field), other information..

Library 2 "Projects" \ Project Name (text), Project Type (single-choice), Involved People (link to People library), other information..

Library 3 "Meetings" \ Date (datetime), Project Name (link to Projects library), Attendees (checkboxes), Discussion (long text), Distribution (checkboxes), other information..

I need to set the checkboxes for both Attendees and Distribution within the Meetings entry. I want Attendees to have all checkboxes deselected by default and have all checkboxes of Distribution selected by default. This is what I have so far:

var e = entry();
var project = e.field("Project").length;
if (project > 0) {
   var linkedEntry = e.field("Project")[0];
   var projectTeam = linkedEntry.field("Involved People");
   var forSet = "";
   for (var act in projectTeam) {
      if (forSet !="")
         forSet += ", ";
      forSet += projectTeam[act];
      }
   entryDefault().set("Distribution", forSet);
   }

When I have a message(forSet), I get a result of [object Entry], [object Entry], [object Entry], [object Entry]. I'm not actually getting the values of the Involved People and I cannot figure out how to pull the names only.

Any assistance would be helpful.

Thanks,

On Monday, August 20, 2018 at 9:52:06 PM UTC-5, Aaron Dahl wrote:

[Bill Crews]

I'm really sorry I didn't respond to your previous post; I intended to but lost it. Let me analyze your script line by line...

var e = entry();
var project = e.field("Project").length;
if (project > 0) {

   var linkedEntry = e.field("Project")[0]; // Should be an Entry object
   var projectTeam = linkedEntry.field("Involved People"); // field() on the Entry object should be valid, but is Involved People a String or or a Link? If a String, why? If it includes commas, you'll fail.
   var forSet = "";
   for (var act in projectTeam) { // Loop should work, though projectTeam.join, ", ") would do it faster

      if (forSet !="")
         forSet += ", ";

      forSet += projectTeam[act]; // 
      }
   entryDefault().set("Distribution", forSet);
   }

See comments above.

The Entry object's set() method, by documentation, takes an array of Strings as the second argument. However, it is true that it appears to also work with a comma-separated String of Strings. However, if your checkbox items have any embedded commas, that way won't work.

I suggest you just use an array of Strings for the second argument. If you need to build that up one at a time, as you are doing above, use the Array object's push() method. No need to join() or concatenate...

var forSet = []; This will work with commas embedded in Checkboxes items.

...and in your for statement...

forSet.push(item); // item should be a String object. Whether projectTeam[act] is a String or not, I leave to you.

I don't know that this will solve all your problems. It seems that projectTeam[act] is an Entry object and not a String. I don't understand why. I would walk backwards through the links to be sure what type each one is.

[Aaron Dahl]

Thanks for your help Bill.

I've been trying to get this figured out all day, yet remain at a loss.

"Involved People" is a link to the People library (many-to-many). I need to only collect the "Name" field of the People library for this Distribution checkboxes, and I would like to use the most efficient process to collect and join the entries. There shouldn't be any reason to use the loop so projectTeam.join(", "); should work fine. While I don't currently have commas in the Name fields of the People library, it is possible we could eventually have commas in the Name field, so I should relay the items as an array to be safe.

I am not understanding how to pass the linked entries of the People library as an array into entryDefault().set(). 

I thought I was defining a string object with this portion:

var object = {};
      object["name"] = projectTeam[i].field("Name");

apparently, I am not. So, although the script editor tells me it is running successfully, I'm now getting [object Object], [object Object], [object Object], [object Object] .. instead of [object Entry]...

What am I missing?

var e = entry();
var project = e.field("Project").length;
if (project > 0) {
   var linkedEntry = e.field("Project")[0];

   var projectTeam = linkedEntry.field("Involved People");

   var forSet = [];
   for (var i in projectTeam){
      var object = {};
      object["name"] = projectTeam[i].field("Name");
      forSet.push(object);
   }
   entryDefault().set("Distribution", forSet);
   }

Thanks,

On Monday, August 20, 2018 at 9:52:06 PM UTC-5, Aaron Dahl wrote:

[Aaron Dahl]

So I was able to find a solution. It's not perfect, but it works and speed up our process. The original code was catching the objects, but I only wanted the Name field. This populates both the Participants and the Distribution field with the names of all Involved People, then deselects all Participants (it actually gives me one blank field that is selected - I cannot figure out how to just set the entire list to none selected). 

Periodically, someone will manually need to go in and delete the items from the Participants and the Distribution checkboxes. At some point, hopefully, there will be a method to purge the list, but this will suffice for right now.

var e = entry();
var project = e.field("Project").length;
if (project > 0) {
   var linkedEntry = e.field("Project")[0];

   var involvedPeople = linkedEntry.field("Involved People");
   var forSet = [];
   var l = involvedPeople.length;
   for (var i = 0; i < l; i++) {
      var names = involvedPeople[i];
      forSet.push(names.field("Name"));
   }
   entryDefault().set("Participants", forSet);
   entryDefault().set("Distribution", forSet);
   e.set("Participants", "");
   }

Bill Crews - thanks for your help on this and other posts which enabled me to piece together this script. I think without your continued support on this forum, many would have sought an alternative solution.

Cheers!

[Aaron Dahl]

So perhaps I spoke/wrote too soon. This script again works fine on the desktop, but does not translate to Android platform (which is where the script is most needed). Anyone who has suggestions on what I need to do to make this Android suitable??

Thanks,