How to sort/filter on condition in related library

[David O'Neil]

Say I have a normalized arrangement with a library named 'States', another named 'Cities' (with a link to the appropriate state in it), and a third named 'Contacts', with a link to the contact's city in it.

How do I filter/sort contacts by the 'State' condition in the State library?

Another semi-related question is how does Memento really link the 'States' condition in the 'Cities' library?  In a truly normalized case, it would contain a link to an ID, but Memento seems to just be copying the 'State' verbatim to the 'Cities' database.  Is it not a true RMDB in this manner?  (As long as it works, I don't care much, but I just want to make certain I'm not overlooking something simple.  Does Memento have the tools to handle normalized DBs with some type of SQL like queries, or can the equivalent be done with Javascript?)

[Bill Crews]

> Say I have a normalized arrangement with a library named 'States', another named 'Cities' (with a link to the appropriate state in it), and a third named 'Contacts', with a link to the contact's city in it.

> How do I filter/sort contacts by the 'State' condition in the State library?

Create JavaScript field in Contacts with this script...

// Assumes link to city in Contacts is City and link to state in Cities is State

field("City")[0].field("State")

Now, you have a field in Contacts using which you can sort, filter, and group.

> Another semi-related question is how does Memento really link the 'States' condition in the 'Cities' library? In a truly normalized case, it would contain a link to an ID, but Memento seems to just be copying the 'State' verbatim to the 'Cities' database. Is it not a true RMDB in this manner? (As long as it works, I don't care much, but I just want to make certain I'm not overlooking something simple. Does Memento have the tools to handle normalized DBs with some type of SQL like queries, or can the equivalent be done with Javascript?)

First, I've been almost entirely unable over the years to have a rational conversation about relational database theory with anyone. Everyone comes from a different walk of life, with different models and success stories in their minds, and they all tend to be very religious about it. So, I'll say that it's pretty damned relational, that Oracle, DB2, Sybase, etc each have their quirks underneath the relational mantle. However, though there could be, there is no SQL engine within or over the top of Memento, and many folks use things in Memento that go beyond relational, like many-to-many links.

Anyway, underneath, there is a relational engine that uses IDs for physical storage, but the real answer is that at the logical level, a link is to an "entry name", which is built from the field role set on defined fields. An entry name is quite like a primary key, and one can set a library to enforce uniqueness of entry names, which enables relational features. It does no "storing" like you suggest.

There is no query engine. The power of Memento comes from its user interface, from the slice'n'dice features of aggregation, sorting, filtering, grouping, and charting, and from extension via JavaScript features and libraries.

[David O'Neil]

Thank you! I was able to finally extrapolate the answer to my real situation with some work: field("Person")[0].field("StationProgramOrOrg")[0].field("City")[0].field("StateOrProvince")[0].field("StateOrProvince")

I assume it would be better from a refresh perspective to place a field in the parent table with the relevant information in it, rather than doing such a deep nest?

On Saturday, December 2, 2017 at 4:47:29 PM UTC-6, Bill Crews wrote:

...It does no "storing" like you suggest...

Perhaps 'duplication' is a better term. From what I've seen, if you link to an entry in a parent table, it duplicates the Text of the entry rather than directly duplicating the entry.  It is smart enough to update the text if the parent table's field gets changed, so IDs are somehow involved in the background, but it is still a duplication that wasn't used in my previous approaches.  I will think about the above Javascript in relation to this some more - it might solve the issue that is probably a non-issue in reality.  The above solved the fundamental problem.

Again, Thank you VERY much!

David

[David O'Neil]

>> rather than directly duplicating the entry

Should have been, "just using the ID"

[David O'Neil]

On Saturday, December 2, 2017 at 6:16:38 PM UTC-6, David O'Neil wrote:

...I will think about the above Javascript in relation to this some more...

Now that I'm starting to understand, just linking to the ID field solves it - duh!

Thanks!

[David O'Neil]

OK, I'm back to the duplication issue. If I create a table called 'Countries', and one called 'States', and 'Countries' contains a field called 'CountriesID' which is an autoincrement integer, and I place a link to the CountriesID in the 'States' table, instead of entering '1' for USA in the States table, it always enters 'USA'.  What am I missing?

[Bill Crews]

There is a lot of literature about coding versus using data from the user universe as primary keys. Again many religious opinions on all sides. Data modelers like me tend to hate encoding real-world information. It's a distraction from what's really going on. But efficiency-oriented folks certainly like to encode stuff. To me the best of all worlds is when you can use real data for keys AND the DBMS is smart enough to convert to & use IDs down inside as needed AND updates everything when a real-world value changes.

[David O'Neil]

That is fine, but is slightly annoying when it doesn't do what I tell it to do. If I tell it to link to the ID, I'm not expecting to see a completely different field in the result. Let me amend that: it is very annoying. If I wanted it to be a duplicate of the first text field in the record (which is my guess as to what it is doing), I would have linked to that text field. If you are indicating that is the way it is, I will learn to live with it, but the documentation should clarify the rules it uses. Does it always use the first text field in this case?

[Bill Crews]

Having written the wiki myself, I beg to differ. It states precisely what happens, and I'll summarize it for you now.

You clearly understand relational concepts quite well; let's delve into this "duplication" issue.

First, unless you take a hexdump of the database, Memento users don't care or generally know what is physically stored in the linking library. What we do know is what we see. What we see depends on what we're doing at the moment. If we are exporting to CSV, we see some things. If we view a list of entries, we see some things. If we view an individual entry, we see some things, and if we view an entry of a library that contains a many-to-many link, we see in the list of subentries some things.

In all cases that are lists (whether using List View or Card View), the fields that are displayed are defined by what is called the role of the field. In a field edit card within the library edit screen, it says "Display in the list as...". The choices are Regular Field, Entry Name, Entry Description, or Entry Status. Fields with role Regular Field are not shown in lists. All other roles are shown, each role in a predefined location in the list bar for the field.

So, when you link, you link to an entry, not to a field. If Countries has a field or two or three of role Entry Name, that is the identifier that is linked to. If you're doing relational work, as you are, you should set all libraries on the MAIN tab to enforce unique entry names. IF ALL YOU WANT TO SEE, WHEN A STATE LINKS TO ITS COUNTRY IS AN INTEGER, THEN JUST MAKE THAT INTEGER'S ROLE ENTRY NAME, AND ALL OTHER FIELDS' ROLES AS REGULAR FIELD. Then you'll see only the integer, though I don't know why anyone would consider that preferable.

THE APP IS NOT STORING ALL THOSE FIELDS; IT IS SHOWING THOSE FIELDS TO YOU IN THE LIST FOR YOUR BENEFIT. You choose what to show and where in the list bar to show it.

If you've been seeing the country name in the list, it's because you must have set the country name text field as Entry Name.

[David O'Neil]

Thank you VERY MUCH! I wish I had found that explanation during my perusals! One of the questions at the back of my mind was "What do those four choices do?" That is super helpful! Understanding the Javascript and that make this a very useful tool!

I assume the answer on the deepness question is "Yes". It is better to break the Javascript into sections in each D B, to reduce the update calculations in the final table.

Again, a huge thanks for your time!