Eliminating Portal in report

[bdam]

Is it possible to merge together several related records?

Here's what I'm trying to do: I have a many-to-many relationship between Authors and Books since a book can have one or more authors. In the books database we see a list of the associated authors via a portal.

This is all well and fine for data entry screens.

However, when doing reports we don't have room for a vertical list of authors, we need them to be listed horizonally. Typically we'll only list their last name.

The three things I can think of are a horizontal portal, merge field, or calculation. The first would involve rotating the orientation of the portal somehow so that multiple records are displayed horizontally. The merge field idea would involve getting some sort of repetition in there. The calculation might be a combination of getnthrecord but how do you know how many records there are?

Are any of these ideas possible or is there another method I could use?

Thanks,

Bryan

[Søren Dyhr]

I would report on the joinfile instead?? Use the two foreign keys as breakers and usher the related values in via the relationship.

--sd

[bdam]

Ok, I see what you mean by doing it in the join table, I can see each record. Now I can see how I could group these and have a sub-summary report but how would I get the last names together?

I'm not sure what you mean by using the foreign keys as breakers. Would you be willing to show me using the attached example file? The report layout is based on the join table "Books_to_Author". Right now we see three authors for the first book. What I'm looking to do is show one record and the three last names of the author.

Thanks Soren,

Bryan

Books_to_Author.zip

[Søren Dyhr]

how would I get the last names together?

I thought I finally have turned nuts, nothing worked as I expected, until I saw that your names were of type NUMBER ...in my humble opinion is text a better type I have also changed elsewher in your fields defs....

--sd

Books_to_AuthorMod.zip

[bdam]

Hahah, that sounds just like me. Forgetting to change the field type. Sorry about that.

I think I didn't explain it right however. I wasn't looking to group all the books an author has done. I'm trying to group all the authors for a given book. It's putting their last names together that I'm struggling with. To better explain here's what I'm looking to do using the records in your latest database (I had to use "_" instead of spaces)

__ISBN__________Authors _____________Title

11111111___Brown, Anderson, Phillips___My First Book

22222222___Allen,Scott, Clark__________My Second Book

33333333___Tompson, Clark___________My Last Book

12345678___Clark___________________ Yet Another

Thanks again Soren,

Bryan

Edited July 13, 2006 by Guest

[Søren Dyhr]

All it takes is reshufling the sortorder so the ISBN comes first then full name!

--sd

[bdam]

Hmm, I tried doing that and still didn't get the ouput I was looking for. I'm trying to get only one line for the whole book, not a header line for the book and then 3 lines below that for the authors.

I've included a couple pictures to show what I'm getting over here. Report_2 is the report after changing the report scipt to sort by ISBN first, then full_name. I also changed the sub-summaries so they summarize when sorted by ISBN. As you can see for each book we're using separate lines here which unfortuneatly won't work on some of our large reports. Report_Wish is what I'm trying to do.

Thanks again,

Bryan

[Søren Dyhr]

Well then just get rid of unwanted breakers...

--sd

Books_to_AuthorMOD2.zip

[bdam]

Hmm, yea but it's not just about the breakers. Looking at the report above from your MOD2. We still have multiple lines for the same book: there's three lines for 'My First Book'. What I'm trying to do is have one line for the book and authors like the Report_Wish in my previous post.

I should mention that this isn't a new database I'm creating otherwise I could just do it the way you've shown here. I am rebuilding FM5 databases and people have been able do this in the past because we weren't using relationships: they just typed in the author name(s) in every time. I just know they are going to complain if their report that took one page now takes 3 because of the extra lines.

Thanks again Soren,

Bryan

[Søren Dyhr]

Ah! I didn't notice that - Authors in plural!!

So this is next stab at it.

--sd

Books_to_AuthorMOD3.zip

[bdam]

There it is, exactly. Thanks so much Soren, you got it. I would never have thought of using the value list that way. I was stuck thinking about portals and how to use them like you used the value list.

Oh, and yes, we have many books, especially the Academic ones, that have multiple authors. To top if off these authors will occassionally want their name to look different on different books: John Smith on one and J.R. Smith on another. Fun Stuff.

Thanks again for sticking it out.

Bryan