Show all records (part 2)

[georgewash]

Previously I had an issue getting all of the records from a portal to show up in an invoice layout. That was solved by changing the layout to show records from the portal table.

Now I am running into a similar problem, but require all records from multiple portals to show up in an 'invoice-type' layout. Here are the details:

Themes:

Theme ID

Type ID

FCP Transitions:

FCP ID

DJ Transitions:

DJ ID

Music:

Music ID

ThemeFCP:

Theme ID

FCP ID

ThemeDJ:

Theme ID

DJ ID

MusicTypes:

Type ID

Music ID

I have ThemeFCP, ThemeDJ, and MusicTypes as portals in Themes and am able to select the fields properly.

I created a layout called Theme Elements which simply lists all the fields in Themes (other fields besides those above also). For the three portals I only get the first record. I've tried making each of the portals the related record in the layout setup but it doesn't change anything.

I am fundamentally not understanding how to display all records from a a portal in another layout. If anyone can point me to somewhere in the forums where this is explained I would appreciate it. Of course any help directly related to my issue is great too.

I have attached a copy of the database for anyone to look at.

Thanks.

Elements.fp7.zip

[georgewash]

Just bumping this post.

Surely this must have been discussed previously or there is an easy solution somebody can share.

Thanks.

[LaRetta]

In all honesty, George, I opened your file to try to assist. I took one look at your graph, gulped in shock, attempted to pull the relationships aside that fit what you described in your post and finally gave up. I can usually reverse-engineer pretty well but what you have is over-complicated and your post is not specific enough. That combination means I can't assist you properly.

If you provide a clean demo file with just the relationship you need to solve ... and pare down your post to just what needs to be accomplished in ONE place, then we can help you with it. That then can be used by you to translate through the rest of your file. Someone here MAY have enough time and energy to pour through your structure; if so, I commend them. But help will come faster (and be more comprehensive) if the situation can be isolated.

LaRetta :wink2:

[Fenton]

Basically, you need to spend some time learning, and organizing. Your Relationship Graph is one of the messiest I've ever seen. It may not be "wrong," but it took me 15 min just so I could look at it properly. It would also be useful for you to learn about the "anchor-buoy" method of organization, to separate some of the spaghetti. Your naming convention seems to start out OK, but then you leave things like "music 2," "music 3," etc.. Which is OK, if you can remember what they are, but otherwise....

But all of that is less important than actual keys and relationships. Let's look at the Music Types portal. You are showing "songs" there, from Music 2. It seems to be that the portal should be based on Music 2. You only have 1 Type per Theme, so a portal to MusicTypes makes little sense (unless you're going to have multiple Types per Theme, in which case you'd need a multi-line field in Themes, or a "join" table).

So, you can change the portal to Music 2 and show songs. However, it's hard to tell what's going on, because the Music Types table, which it goes thru, is confusing. There are 3 "id' fields in the table. The name field is an empty calculation.

The Themes TypeID field is in the portal. Which is just plain wrong. If MusicTypes is a join table, it should be a field local to MusicTypes.

Sorry to be harsh. But it needs some work. I think you've got a lot of the hierarchy correct, but confusion with the ID fields is making it not work correctly. For now, you should likely show the ID fields in the portals, rather than just popups, so you can see what they are.

[georgewash]

Fenton and LaRetta,

Thanks for taking the time to look at my file.

Yes, I realize it is a total nightmare.

It started out as a file someone posted on the forums 3 years ago turning the BPK into a single file. I've since taken bits and pieces from the original BPK, other people's help files, and my own additons. Then I realized I needed to split my original file into 2. As I've gone along I have not cleaned up the messes I've created. Somehow it actually is becoming very functional.

For the question I posted, the relationships are simply copied from a file Beverly did to help me extract the correct songs via its types in Themes. Her solution was not exactly what I needed and I haven't taken out the extra bits yet. My problem is I don't understand how to get a layout to display all the records from multiple portals.

Fenton, you mentioned multi-line fields in your post. What is that? It's the first time I've heard of it. If there is a simple way to display multiple records instead of a portal that would probably help me quite a bit.

I will clean up my file and properly name everything - it needs to be done eventually and now is probably the best time. In the mean time if anyone can point me in the right direction as to how display multiple records (in a generic sense) I would appreciate it.

Thanks again.

[Fenton]

My problem is I don't understand how to get a layout to display all the records from multiple portals.

Our problem is that we don't know exactly what you mean by this. You've already got multiple portals, each displaying multiple records.

The Music Types portal is not done correctly, as evidenced by a local field from the parent layout's table occurrence in the row (which is always wrong). But we don't really know what its purpose is, and it's too confusing to guess.

As LaRetta pointed out, it would be better if you posted a small file, with an example of what you're trying to do. Or cleaned up your file considerably, then asked a more specific question.

[LaRetta]

My problem is I don't understand how to get a layout to display all the records from multiple portals.

If you want records from different tables/files in one portal then it indicates STRONGLY that those records should have all been combined to begin with. If they are not combined, you can import all the tables into one temp table (if only to import only their ID). Then base the portal on the temp table.

I also question whether you have related them properly; as Fenton points out, you don't seem to grasp relationships and IDs correctly. If you did, you wouldn't have 'like' records (records which should be combined in a portal) in separate tables/files. If properly structured, your question probably wouldn't even exist. But truly, I'm just guessing what you mean because I don't really understand what you have.

We want to help, George. I look forward to a clear example from which to work. There are many wonderful people here to help you with it!

L

[georgewash]

I guess I'm not too clear as to what I'm running up against.

My portals work fine. It's when I try to get all of the records from the portal to show up in a list style layout that I can only get one of the records to show up.

I'll get my file cleaned up and let you know exactly what I am talking about.

Thanks again, I really appreciate it.

[Vaughan]

"It's when I try to get all of the records from the portal to show up in a list style layout..."

That's the problem exactly. A layout can only display records from one table occurrence.

LaRetta mentioned earlier:

"If you want records from different tables/files in one portal then it indicates STRONGLY that those records should have all been combined to begin with. If they are not combined, you can import all the tables into one temp table (if only to import only their ID). Then base the portal on the temp table.

Another option is to print each layout (set of records) to pdf sequentially, appending each set to the same pdf file.

[georgewash]

Thanks for continuing to help with this.

I don't see why these portals should be (or could be) combined into one. The data they are using is from different tables.

The idea of creating a temp portal to get everything into one portal to be used in the list layout is interesting, but I am not sure how to go about it.

Maybe a better explanation of what I have so far will help.

If you look at the Themes layout in Browse Mode, you'll see that its purpose is to select various fields that make up each 'theme' (record). Part of the selection process uses 2 portals. Why use portals? Because I don't know of another way to choose more than one item from a given table occurrence. Secondly, these portals are restricted to only show 'transition' records from these table occurrences because those are the only types available to be chosen in Themes.

The thing is, Themes works exactly like I want! There have been various comments about the logic being faulty. I grant that I don't understand everything perfectly, but the only way I've gotten it to this point is to follow the advice and examples from people like Comment and Beverly who I trust as being experts.

Is my real problem that I am expecting something that is not possible in FM? It just seems as if it should be possible to take all the information in a particular T.O. and get it to display/print in a list, even if there are multiple portals involved.

I have cleaned up the Relationship Graph and have reposted the file, if it will help anyone.

BTW, I am getting an error every time I try a search so I can't even try to find any help on my own via the forums right now.

Thanks again everyone!

Elements.fp7.zip

[georgewash]

Our problem is that we don't know exactly what you mean by this. You've already got multiple portals, each displaying multiple records.

The Music Types portal is not done correctly, as evidenced by a local field from the parent layout's table occurrence in the row (which is always wrong). But we don't really know what its purpose is, and it's too confusing to guess.

Check out topic #199071 (not sure how to make this a link), to understand what I want to accomplish with the MusicTypes portal. Basically, one picks the music type for the Theme, and MusicTypes simply displays all songs with matching type.

I had a hard time following the logic behind how this worked from Beverly's example, but I couldn't get it to work any other way.

Thanks.

[LaRetta]

I have spent quite some time this morning trying to understand your structure. I have no idea what ThemeMusic3, connecting to Themes, connecting to ThemeMusic, connecting to Music3 even means! You have multiple occurrences like Music2 and you are using them but, like Fenton attempted to explain, we have no idea what it means.

The file you provided lacks properly related records. I have no idea whether ThemeDJ is a join table to Digital Juice as many-to-many or whether it is a 1:n as regular relationship because there is no proper data within it. You have 23 (out of 26) invalid ThemeIDs in the related data - only ONE is valid 112)! No other theme records show ANY related records! I suspect you would have been better off to create your own solution from scratch than to attempt to modify an existing one. It is obvious that you do not understand what you even have here and Fenton is correct ... you do NOT understand how to use your IDs.

You can try concatenating the fields you wish to display (in digital juice, for instance) and the create a calculation in Themes as List (DigitalJuice::thatCalc) and placing THAT on the layout.

The thing is, Themes works exactly like I want! There have been various comments about the logic being faulty. I grant that I don't understand everything perfectly, but the only way I've gotten it to this point is to follow the advice and examples from people like Comment and Beverly who I trust as being experts.

I find that statement insulting. Fenton, Vaughan and I have spent quite a bit of time trying to assist you. I am done. I suggest you hire Beverly or Comment if you feel they are the only people worth listening to. And in future, begin your posts with: "Only Comment or Beverly please respond ... the rest of you aren't experts or worth listening to." That sure would have saved us SEVERAL hours of our time.

[comment]

If anyone should be insulted, it should be me and Beverly. I am not even in this thread (or the previous one), and I am certainly not responsible for the way this file looks. Furthermore, I suspect "Beverly" is actually Barbara, so that would make it even worse.

Anyway, I think it would be best to keep to the issue and ignore the non-essentials. Just my 2 cents.

[georgewash]

Geez. I certainly did not mean to insult anyone. I am very grateful for all of the help I've gotten on these forums. I guess that can be the problem with email and forums - no way to hear a voice or read body language.

I am truly sorry that you spent so much time trying to decipher my mess. My process has been to attack each issue as they come up, find out how to make it work and then worry about correcting the naming, getting all the ids correct, and filling in the data. Apparently this is not a very good method as I am finding out.

My only reason for stating that what I have so far works is to avoid people from spending a bunch of time going into detail in my file. I thought there might be a big picture point that would help me understand if what I am trying to do is possible and what the approach would be. I thought it might help to look at the file in browse mode to see how it works, not to delve into the relationships.

Comment and Barbara (why did I keep thinking Beverly?), sorry to drag you into this. I was only trying to let others know that much of what I did came from your generous help, not to make you look bad.

I guess the gist of all this is that trying to get multiple portals to be displayed in a list layout is not straight forward. I'll try to look through other discussions and see if there is another way to achieve what I am after. I have much to learn. I need to get beyond looking at everything as a join table and portal.

Again, sorry that I offended some people. I will try to be make sure my comments more accurately reflect my sentiments.

[LaRetta]

If there was a way to pull multile portal records into one table, we would have told you about it. I was trying to find out whether those portal records also had multiple records attached to them. You certainly didn't want the data from ThemeFCP when there were only IDs in them. So not only did you want multiple related records from several relationships but you actually want THEIR children (from what I understand).

I have worked to understand the relationships (and that is critical) before I can suggest how you might achieve what you want. You seen to think relationships aren't important in what/how the data displays but it can't be done without that understanding. I still have no idea WHY you want it in list view; it seems you are using the wrong layout type. You can search all you want. You will find the same answers - one cannot pull multiple portal records from multiple relationships into one LIST layout unless you concatenate each of the related records' fields and then use calculations to cluster them together. I again asky WHY do you need this?

I truly suggest you hire some help here.

[georgewash]

Thanks LaRetta,

I will look into concatenating and using the list function.

It sure will help if I explain WHY I am asking all of these questions!

Themes is used to select various elements. It was designed so that the user can only select the records that are appropriate from other table occurrences (Theme FCP and ThemeDJ only allow 'transitions' from FCP and DJ to be selected). It also displays songs with the same type that is chosen by the user. I suppose a list function would be better than a portal (but I don't really know how to use a list function!).

So, what I want is to simply DISPLAY & PRINT an individual Theme's elements. That's it. Just a simple list of everything that was chosen or is available for each Theme.

Thanks.

[comment]

It's quite possible that this is a task beyond your present capability. There's no shame in that, we all have our limitations which we move by learning.

If I understand this correctly (?), the perfect solution would be to have a master table for all elements, and sub-tables for the individual element types. This has been discussed several times quite recently, see:

http://fmforums.com/forum/showpost.php?post/296106/

http://fmforums.com/forum/showtopic.php?tid/198682

However, this is definitely not a task for beginners. Meanwhile, you could get by making some compromises, i.e. either put all the elements in a single table with a type field and enough fields to describe all possible types, or keep them separate and give up the requirement to see them all in a common portal/list (you still CAN print out and/or export such list by using multiple portals).

[Vaughan]

... the only way I've gotten it to this point is to follow the advice and examples from people like Comment and Beverly who I trust as being experts.

I am not even in this thread...

Comment, you don't have to post any more. You only have to *read* questions and the answers travel directly to the poster telepathically.

[comment]

Good. Then you know what I said.

[georgewash]

Boy, I sure stirred up a hornet's nest.

I was not referring to his help on this specific topic but on many others to other questions I had.

I was simply trying to convey that I did not follow why some of my logic was flawed and that I had gotten to this point by following previous advice from some very talented people.

I promise to be more careful with my comments in the future.

[georgewash]

I took some time to work on other projects and then took another look at this particular issue.

After a bit I am able to get pretty close to what I need by concatenating the fields and using the List function.

Now I need to figure out how to format the List function if possible.

I certainly appreciate all the help. I still have a long way to go, but I'm understanding a bit more all the time.

[Søren Dyhr]

I don't see why these portals should be (or could be) combined into one. The data they are using is from different tables.

Isn't is obvious that's the core problem here? How was these tables established in the first place? Hunches to what goes to which classification, but not really an established method such as normalization.

My take here is that it's one of the most common mistakes with database development, I have done so and the ones I do business with have this decease in their up and present running application ... so I would dare say we all have been there.

It's not obvious how to cure a solution suffering from such a fundamental normalization error, when it's up and running already. The thing you have to learn here is:

structure of data doesn't have to match the presentation

But I can suggest you rock you fixed beliefs by watching this movie:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

....and then temporarily use this hack, to remedy the accidental structure:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

--sd