Can you achieve three levels of indirection in collections without code?

[richard28494]

Hi,
I am building pages to serve details of the journals for a music institute. I want to get three levels of indirection. I have a ‘works’ table, a ‘journal’ table and an ‘article’ table. Articles appear in specific journals. I also have a ‘works in article’ table, because articles may be about several works.

I want to use a control (I have it working from a table) that enables a user to select a work, and have a repeater populated with all the articles about that work. I have that working. The details that I want to report about the article include things like the page number and the journal it is from, as well as obvious things like an author, title and article precis. I do not see fields from the journal table (I want the journal name & description) when I try to wire up a field to a data source. I assume that is because it is three levels of indirection from the work that the user selects in the table: the work table references the ‘works in article’ table which references the article table, and the article table references the journal it is in.

Can this be done without code (I am not really a techie)? Or will I have to resort to nasty hacks like duplicating what should be journal level fields into the article table?

[CODE-NINJA]

Reading your issue, i did understand —> ZERO!
You are writing in another code-language i do not understand .

Please subscribe your problem without EXTRA-INFORMATION of your own.

1. How much databases are used?

2. IDs of DATABASES ?
   3 What is stored in the DATABASES ?

3. How your DATABASES are connected to each other? (what is the connection-ID/column/reference)?

4. How does your process work?

5. When i click on …

6. Then something should happen

7. I need the data of … to be in … when … and so on.

8. But if … then … should not …

9. Want to filter … (what? ) when waht is pressed or happened ???
   …and so on…

This is a clear description of your issue.
Simply just the facts of your project and the project-flow, nothing more.

Perhaps even some pics of your database-structure, or other relevant and importand stuff of your project.

Perhaps THAN, you will get an answer.

[richard28494]

I thought I had done that. Obviously writing the use case in English rather than techie speak is the wrong way to go. So;

I am reporting on articles from music journals. The tables in play are:

1. Journal: JournalID (Primary Key); Journalname; other fields

2. Article: ArticleId (Primary Key); JournalId (Reference to Journal); Author (Reference to Author); Title, Page #, Precis, etc.

3. ArticleWork: ArticleWorkId (Primary Key); ArticleId (Reference to Article); WorkId (Reference to Work)

4. Work: WorkId (Primary Key); Title, description, etc

5. Author: AuthorId (Primary Key); AuthorName; other fields

The requirement in both use cases is to populate a repeater block with details of an article. We want fields including Journal name, Page, Article title, Author, and other article-level fields like a precis.

In the first use case, the user will select an Author from a table of authors. The repeater will be populated with articles the author wrote. Two datasets: Author and Article, with article filtered on the selected Author. Works fine.

In the second case, the user will select a work from a table of works. The repeater will be populated with articles about the work (i.e. articles with a record in the ArticleWork table for the work). I have tried with just two datasets: Work and ArticleWork, with ArticleWork filtered on the selected WorkId. When trying to assign sources of fields for the JournalName and the Author, the fields are shown in the dialogue box, but are greyed out, with no explanation. I expect it is because there are too many levels of indirection. I have also tried to add a third dataset which did not work: I got all repeater blocks reporting fields from the first match.

I suspect this is one of those “we didn’t write that bit” issues. Wix will happily ‘lookup’ a journal name when there is an active dataset for Article, but not where the reference to Article is indirect through ArticleWork. (in this case, I can see that is not an awful restriction to have added - someone might extrapolate my example to many mor levels of indirection - but they should not add such restrictions (if that is indeed what has happened) without documenting the restriction). I have tried to add another dataset for Article in use case 2. That did not seem to fix the problem.

There are work-arounds, but they are a bit messy. I could replicate Journal-level data such as the JournalName into the Article table, so the extra lookup is not required. I could change the primary key of the Journal table to be the JournalName (which should all be unique), but we will hit issues with sort sequences (e.g. #101 sorting before #20, unless we number them #020, which users will think is naff).

So, not a show-stopper, but I would like to know (and I would like Wix to document this sort of thing - their documentation is awful on issues like this).

[CODE-NINJA]

@richard28494
Ok, i will take a look at this tomorrow.
This will surely take more then just few minutes.

[richard28494]

Well, it seems to be even worse, than I feared. I could understand, though still be unhappy, if they had restricted it to just the one level of indirection. So I built a test case where instead of having a typical ‘key’ for the field that is the primary key in one table and the reference field in another, I used user-friendly names. Sufficiently user friendly that if I used those fields on the repeater as the author name and name of the journal, they would be perfectly happy. Thos fields are in the article and author tables, which we know Wix happily looks up to to get values like page number in these sorts of circumstances. And, guess what: those fields are greyed out when you try to select the source for a fied on the repeater. That strikes me a lazy programming: they have the values for that field available, but they are in their internal ID format. It appears that rather than do the lookup from that ID to a user value, they just said ‘tough’.

So, unless you find something I did not find, I appear to have to replicate values (a user-friendly journal name and author name) from the Journal and Author tables down to the Article table.

To say I am unimpressed is an understatement.

[yisrael-wix]

@richard28494 Perhaps share your code so we can offer assistance. You are explaining lots of details, but without code there is no context.

[CODE-NINJA]

@yisrael-wix
I think Richard will not have any code.
He tried to do this as maximum without code (on my opinion).
But my question is, if this is possible to construct his project without any CODE.
I tried but could not do it without CODE.

So i started to generate an example…(but is still in development xD)

https://russian-dima.wixsite.com/meinewebsite-2/music-articles

import wixData from 'wix-data';

$w.onReady(function () {});

export function BTNarticles_click(event) {
 //let VALUE = "Articles"
 let DATABASE = "Articles"
    load_Data(DATABASE)
}

function load_Data (DATABASE) {
    wixData.query(DATABASE)
//  .contains(DATABASE, $w('#dropdown1').value)
    .find()
    .then( (results) => {
 if(results.items.length > 0) {
 let firstItem = results.items[0]; //see item below

        console.log(results.items)
        $w('#repeater1').data = results.items

        $w("#repeater1").onItemReady( ($item, itemData, index) => {
            $item("#ID").text = itemData.articleId;
            $item("#TITLE").text = itemData.title;
            $item("#IMAGE").src = itemData.articleImage;
            $item("#PAGE").text = itemData.articlePage;
            $item("#DESCRIPTION").text = itemData.articlePrecis;

       //     $item("#image1").onClick( (event) => {
 
       //     } );
        } );

        } else {
        }
    } )
    .catch( (err) => {
 let errorMsg = err;
    } );
}

Generated some DATABASES (Articles/Journal/Author/Work)

[richard28494]

@yisrael-wix There is no code. Just tables and trying to build a repeater. No other context needed.

[code-queen]

@richard28494 Try showing screenshots of your database, dataset and page elements to decipher what you are trying to explain.

The best guess I can come up with is: you are trying to connect multiple reference fields that are not properly referenced therefor they cannot be selected. And/or you are using an element(s) that is/are not optimal to reach your goal.

The more you show, the less guessing the community needs to make in order to guide you a little more efficiently. Screenshots are especially helpful at the beginning of your Corvid journey as they can help illustrate the problem a lot faster than words alone.

[richard28494]

How will screenshots help? I have explain the schema of my database, and walked through two use cases, one working and one not. I really do not see the point in ‘guessing’ when I have fully explained.

What we really need is someone, perhaps from Wix, who has access to real documentation, not the ‘fluff’ I find online, which lays out the limits and validation. It must exist, somewhere. In the mean time, given that my second use case does not work, I will replicate data from the Journal and Author tables down to the Article table, so the Wix code does not have to do the extra join. I know that should work because both my use cases are already successfully reporting on other fields (such as page number and aticle precis) from the article table.

[richard28494]

Well, for what it is worth, I have now added fields to the article table that are replicated down from the Journal and Author tables, so Wix does not have to do another join/lookup. Unsurprisingly, it now ‘works’ for both my use cases, but I will have to manage the replication process. Not exactly a clean solution, but we will only have to do it once to load the backlog of all our journals, and then more faffing about every time we add a new journal.

Bah, humbug.

[richard28494]

Hm.

I have just stumbled across multiple-item reference fields. In my schema, I have an ArticleWorks table because an article can be about multiple works, and when I was taught how to model that sort of thing 25 years or so ago, that was how you did it.

I have now found that Wix supports multiple-item reference fields. The overviews I have read seem to imply that this is equivalent to creating tables like ArticleWorks, by supporting multiple entries in the Works field of the Article table (and vice versa). If they are genuinely equivalent (and there are no nasty surprises about limitations in their use which make them not equivalent to tables like ArticleWork) then that would be another way around this restriction with looking up, since the two datasets I would need to report on all articles that are about a given work would be the Work and Article tables, and when reporting from the Articles table we can ‘lookup’ fields from the journal the article is in, such as the Journal description, and the Author of the work, such as their name.

So I can see that having rebuilt this report (just a prototype, fortunately), to replicate down fields into the Article table, just so I can report on them, I am now going to have to rebuild it again, to try to make it work with multiple-item reference fields.

I can, however, foresee one nasty. There is already the user-hostile mechanism for loading reference field values that have to be in the ID format. I have seen nothing in the documentation that I have read that says how you can load multiple values into a multiple-item reference field. Doing it manually in the cludgy interface (where, for example, for records towards the end of the table pop up a tiny dialog to select the value of the reference field, and you cannot scroll to get a bigger dialog. I have found myself sorting the table into the reverse sequence, just to get those records near the top, so I can find the value I want) is a non-starter in the real world.

This post: https://www.wix.com/corvid/forum/community-discussion/importing-and-exporting-collections-with-multi-item-reference-fields tells us:

1. No documentation can be found
2. You cannot import into a multiple-item reference field
   Unfortunately, neither of these statements surprises me. Just 2 weeks into playing with Wix and I am getting cynical already. I guess things might have moved on beyond that post, but I have found nothing. In the circumstances, I guess playing with multiple-item reference fields is probably a waste of time.

[code-queen]

@richard28494 With my most sincere and direct honesty, let me try to explain: the top coders cannot understand what you have on your screen, what your goal is and what suggestions to offer you because you are giving too much that is not making sense.

You need to keep your questions short. By showing a screenshot you can help keep it simple. For example: In this picture i did this and this. My goal is to do that. How do I fix it. Or how do I do that.

Instead you are writing down all of your thoughts, opinions and guesses as to why things do or dont work. Or how you did or did not do something 25 years ago. You may understand yourself because you are looking at your screen and actively work on your site. But at this moment …we still have no idea what needs to be done since we are still unsure what you want to accomplish in the first place.

So if you could re-organize your thoughts into shorter and concise questions you may receive better responses from the community.

(Also, try to remember that the majority of the community is made up persons who volunteer their time to help others. Chances are slimmer that they will stop to read and reply to paragraphs worth of information vs posts that keep it short.)