Obtaining the number of entries (count) from a related library

[Marc A]

Hi,

I've been several hours trying to solve a question.

I have:

1.  an Organizations library (a Master I plan not to be working much time with)
2. an Opportunities library where I work constanly with new and old Opportunities (a Pipeline)
   

Both are related, and I've chosen that the main table is the Opportunities, as it is the one I'll be always working with. So I created a One-to-Many relationship from Opportunities to Organizations. This way, when I create an Opportunity, I can directly assign an Organization to that Opportunity. And I have other advantages I need in choosing this, as I can access the Organization related fields with field("Organizations").field("xxx")

The problem is that I want to have a view from the Organizations library that shows how many Opportunities I have for each Organization. This may seem trivial, it is a simple count of the related fields.... but it appears not to be so simple.  I've tried to do it with Javascript and Calculations, but as the relationship One-to-Many goes from Opportunities to Organizations, I cannot access the equivalent "simmetrical"  expressions like field("Opportunities"). The relationship appears not to be SIMMETRICAL. It means I cannot use an expression like field("Opportunities").length to count and show the number of opportunities for each Organization.

Maybe there's a solution, but I've tried it hard and read along the forum and I 've found nothing going the reverse way of a relationship.

Obviously, there is a solution: that is creating a second relationship from Organizations to Opportunities (in this case a Many-to-Many)... but then you WOULD HAVE TO REENTER again all the relations (!!!). I don't know why, but it seems a design consequence.

Any help will be very, very much appreciated!

Thanks in advance

[Bill Crews]

Try programming a library action (wiki page Actions). Use libByName() to connect to your Opportunities library (wiki page Memento JavaScript Library, Object Library). Use opplib.entries() to get an array of those entries. Use oppents.length to get the total number of those.

If you iterate over the oppents, you can build counters for subtotals. It'll be up to you in the user interface to Sort, Group, and Filter the list to your liking. Any other field values from oppents that you might want to group on or whatever can be fetched during your loop, also. You'll define new fields in Organizations to hold all these values.

I don't remember where the best examples for this are; maybe in Actions, but the Trigger Examples page has the most examples, and one of them might be better.

[Marc A]

Thanks Bill. I appreciate very much your support.

My intention was not putting my hands on JS, as I began with Memento just 3 days ago and I've never programmed in JS... but I think I needed to give it a try after your help.

So in fact it was quite easy to implement what you suggested, and it works perfect:

var libOps = libByName("Oportunidades"),

    libEmp = libByName("Empresas"),

     entOps = libOps.entries(),

     entEmp = libEmp.entries();

for (var i in entEmp) {

     nameEmp = entEmp[i].field("Nome");

     count=0;

     for(var j in entOps)

       for (var k in entOps[j].field("Empresa")) {

            if (entOps[j].field("Empresa")[k].field("Nome") == nameEmp)

            count += 1

           };

     entEmp[i].set("NumOps",count);

}

For sure it is not optimized, but it works as you pointed out.

I guess I was in a completely bad path trying to do it in a JS field, and the solution was to implement it as an Action. It seems within the JS fields of an entry you can't have access to other libraries (unless they are directly linked in the same library of the JS field)

Thanks very much Bill!!!