Major Detail on a Child layout/table, not the Parent

[Christoph]

I'm new to this forum and even newer to FM... so this makes me a beginner but hoping to learn quickly.

I've been hoping to find instructions on how to create a master-detail layout, not on the parent table. It seems that with many examples found, the master detail is design to work on the main page, which means when you create a new record, it creates a new field in the portal row and you add the specific name in that field and on the detail side, all data pertaining to the portal row name. I can get this to work with no problem.

Where the problem comes in is here...

I'm creating a songwriter database. The main opening page is designed to include the song title, artist name and all data pertaining to the song (producer, writer/publisher splits, etc) I call this layout "Song". Each new record will store a new song, artist name, etc...

Now, in the Header, I added a Button bar. One of them is labeled "Pitch". The Pitch layout is designed to work with individual song titles or records, coming from the "Song" layout. This Pitch layout will include a left side portal and towards the right, detail info pertaining to each portal row. This is where I'm stuck. I'm not sure how to set this up. Any help would really be appreciated.

Thank you.
 

[Lee Smith]

Hello Chris,

It's unclear to me what your question is, but here is an example of a "Master Detail" example.

[Christoph]

2 minutes ago, Lee Smith said:

Hello Chris,

It's unclear to me what your question is, but here is an example of a "Master Detail" example.

Hi Lee, I'm not seeing any example within your post. Although I've seen, watched and read through several examples of a Master Detail, none of them are related to what I'm after. Most Master Detail layouts are on the main page therefore, when you create a new record, it creates a new portal row. In my case, I would like the MD to be on a totally different layout but associated to the main layout. If this is still confusing, I will try to upload images of what I'm after. Thx.

[Lee Smith]

I forgot to attach it, I was eating dinner. It is now linked to the Blue Text in my first post.

[Christoph]

12 minutes ago, Lee Smith said:

I forgot to attach it, I was eating dinner. It is now linked to the Blue Text in my first post.

Thanks for the file, Lee. I have seen this file before. This won't work for what I'm after. From what I understand, this Master Detail MFM Module "portal rows" is designed to work with records being created. So for example, when you create a new record, it creates a new portal row. For lack of better word, I would like my MD to be a secondary layout to my main one. 

[Christoph]

Hi,

I'm not sure if the attached FM mock up will help but I hope someone can have a look at it and will understand what I'm looking for. Also, I'm not sure if this thread belongs in the Portal forum... I appreciate any help.

Thank you.

Test File Demo.fmp12

[bruceR]

It's a mess. The "Library" and "Album" and "Song" tables are the same table.

You don't have a separate Song table. The Song Title is set to always auto-enter "" into itself.

There are established solutions which do this. 

[Christoph]

Thanks for the confidence, Bruce  

Yes, there are issues of which, me the beginner, is trying to iron out but I am slowly learning and they will get fixed.

The reason why the Song Title is set to "" is because I was told by a FM professional to add this when I have to duplicate a record, minus the song title field. The real big picture design is missing in what I uploaded.

I know there are solutions which do this but I'd prefer not to use them. I know in what I'm asking, it really is a simple fix and I was hoping someone would help.

[bruceR]

Quote

 I know in what I'm asking, it really is a simple fix and I was hoping someone would help.

That isn't the way it looks to me. Looking at your description and your file is like looking at what used to be a functioning car. Now the spare tire is in the back seat; the steering wheel is attached to the left fender; and the windshield wiper are attached to the passenger door handle. 

No, I don't understand your vision of where you're going with your file. No, I don't think the solution is simple. 

[bruceR]

Here's cleaned up graph screenshot though.

[Christoph]

Thanks for the cleaned up graph, Bruce.  Now I'm assuming this is purely cosmetic and the spare tire is still in the back seat, steering wheel attached to left fender, etc? By the way, the table occurrences in the image is actually a file from FM but for my needs, it seemed rather limited and thought to expand.

If you're not understanding my vision then I must not be explaining myself properly... although I feel I couldn't have been more clearer. I may not know all the FM lingo but I'm not quite sure how to better explain that "TV & Film Pitch" is a sub-category of "Song Info".

 

[Lee Smith]

Hi Christoph,

The FM Forums welcomes members of all skill levels. The only requirement being that they have familiarize themselves with the FileMaker basics.

I opened your file, and the first thing I did was to organize the Relationship Graph. The Relationship Graph is not a ERD, however, it does give you insight to the file structure. The ERD should be setup when you are starting a new solution.

I would recommend that you study what an ERD does, and how it fits into developing a databases. If you are not sure what an Entity Relationship Diagram (ERD) does, do a search for either ERD or Entity Relationship Diagram. This is just one of the hits that a  Google Search found. https://www.lucidchart.com/pages/er-diagrams

Here is a free ERD tool that is available at https://www.draw.io However, you can do one using a pen and paper.

There is a FREE training class being provided by ISO FileMaker Magazine  https://www.youtube.com/channel/UC92MpDPgdr-xEn3JRUAmZOQ

I wish that someone had provided classes like this when I first started to develop in FileMaker,

There are currently 4 Areas being covered by Matt Petrowsky so far, Environment, Fields and Calculations,  Data Structures  , and Layout Mode and Design, The Data Structure Lessons would help you a lot with Relationship Graph, He has spent a lot of time covering this section.

There are other training sites that offer training, I can send you a list of them if you want.

HTH

Lee  

[Josh Ormond]

20 hours ago, Christoph said:

Thanks for the cleaned up graph, Bruce.  Now I'm assuming this is purely cosmetic and the spare tire is still in the back seat, steering wheel attached to left fender, etc? By the way, the table occurrences in the image is actually a file from FM but for my needs, it seemed rather limited and thought to expand.

If you're not understanding my vision then I must not be explaining myself properly... although I feel I couldn't have been more clearer. I may not know all the FM lingo but I'm not quite sure how to better explain that "TV & Film Pitch" is a sub-category of "Song Info".

If I get what you are trying to do:

1. You have to create a new record in order to get a new row in the portal. You many need to split the info in the portal out into it's own table. But I would need a little more info to understand exactly what you are trying to do with that.

2. To select a portal row and have the details show on the layout of the local record, you need some kind of relationship to get the field to show that record's data. Or as a master detail, move the current record to the one you clicked on.

We definitely want to help, so don't give up yet.   

[Christoph]

Hi Lee,

Many thanks for taking the time to offer valuable resources and get myself better acquainted with FM. I am in the beginning learning stages and although I've watched several video's, there's never a lack of how to better yourself and get the best out of what I'm after or anyone else for that matter. Matt Petrowsky provides excellent classes as well as Guy Stevens. Both of them have been my "go to" training and my skills have improved. I will definitely go through the links and study up  

Once again, thank you!

Regards,

Christoph

Edited February 2, 2018 by Christoph

[Christoph]

3 hours ago, Josh Ormond said:

If I get what you are trying to do:

1. You have to create a new record in order to get a new row in the portal. You many need to split the info in the portal out into it's own table. But I would need a little more info to understand exactly what you are trying to do with that.

2. To select a portal row and have the details show on the layout of the local record, you need some kind of relationship to get the field to show that record's data. Or as a master detail, move the current record to the one you clicked on.

We definitely want to help, so don't give up yet.   

Hi Josh,

Thank you for looking into this as well 

A few days ago, I was able to get my solution to sort of work with creating a new record, which gave me a new portal row but when I did that, it took me away from the current record (or in this case, Song Title). Is there any other way around it where when I create a new portal row, I am still on the same record (Song Title)? Thx.

Cheers!

[Josh Ormond]

Define "took me away from the current record".

[Christoph]

43 minutes ago, Josh Ormond said:

Define "took me away from the current record".

Hi Josh,

Thanks for the quick response.

Sometimes I’m better explaining with a diagram because of my limited knowledge but in the image, this is exactly what I’d like FM to do. The current record (or song title) in the “Song Info layout” is the same current song title in the “TV & Film Pitch layout”. They work together. I can pitch a song to multiple TV shows, without having to create a new record, as I have designed in the enclosed diagram.

I know I can do this another way. For example, create a single layout, add the Song Title field and other fields pertaining to the song. Then, I include a portal (on the same layout), add the TV Show title and other fields, and when in Browse mode, when I add a new TV show title, it’ll create a new portal row for another TV show and all its data but I'd rather not have it this way.

Does this make sense? Thx!

[Josh Ormond]

See if this is close. 

I added a couple relationships. 

• one to add new TV SHOWS or FILM TITLES to the portal.
• one to select the show or film in the portal...which allows the fields on the right to display data from portal  ( and show the correct data ).

I added a step to the Highlight script.

• It sets the global field, that links the current record to the one you clicked on in the portal. Which also allows the you to display the data from the portal in the fields on the right.

Dig into it. Let us know if you have any questions. As Bruce had mentioned, it would be wise to make a copy...then clean out stuff you are not using. 

• There is a script trigger in the portal that was not linked to an actual script.
• Some relationships are not used ( at least in this file )
• etc.

Test File Demo.fmp12

[Christoph]

Josh!!! You did it!! Woot woot!! (doing the happy dance)  Thank you soooo much! Beautiful work!!

I knew this could be done and later this evening, I will try to follow your work to incorporate into the actual FM file.

Once again, many thanks!

Cheers!

[Josh Ormond]

Good to hear. Post back if you have any questions about how it works.

[Christoph]

57 minutes ago, Josh Ormond said:

Good to hear. Post back if you have any questions about how it works.

You got it, Josh and again, thank you. Works like a charm  

[Christoph]

Hi Josh,

I thought I'd work the Pitch layout a little more by adding another portal into a slide control. When you have a moment, could you please have a look at the enclosed file again and see where I went wrong?

When in the TV & Film Pitch Layout, and you go between the portal rows "Mash" and "Big Show 2", the Notes field follows correctly... but when you go to the next slide control layout, the portal/portal rows don't seem to work properly when you go between the 2 show titles. I've tried to connect table occurrences but still a no go.

Thanks a mill!

Test File Demo 2.fmp12

[Josh Ormond]

Your creation point is different. Because the Film Pitch Notes belong to the Film Title...you have to create it using a relationship to that table.

Then you can use another global field to select that film to be the basis for which records you want to see.

Test File Demo 2.fmp12

Note, that the Film Pitch Notes will be created being related to the film you have selected in the Film Pitch Portal.

[bruceR]

May I suggest that you work with this version going forward. It clarifies key field names, reorganizes the graph, and eliminates "portal" from any table or relationship names. 

The TO may be used in a portal. But labeling the TO as someThing_someThingPortal hides actual relational concepts.

Test File DemoJOBR.fmp12

 

Edited February 3, 2018 by BruceR
update graph image

[Christoph]

3 hours ago, Josh Ormond said:

Your creation point is different. Because the Film Pitch Notes belong to the Film Title...you have to create it using a relationship to that table.

Then you can use another global field to select that film to be the basis for which records you want to see.

Test File Demo 2.fmp12

Note, that the Film Pitch Notes will be created being related to the film you have selected in the Film Pitch Portal.

Hi Josh,

Ah! I see where I went wrong and when I look at the graph... it all makes sense now.

What also threw me off is that some relationships aren't being used, as you mentioned earlier on, and they will be removed at some point. It'll make things easier to understand.

Thanks again for your expert advice and work. 

Cheers!

 

3 hours ago, BruceR said:

May I suggest that you work with this version going forward. It clarifies key field names, reorganizes the graph, and eliminates "portal" from any table or relationship names. 

The TO may be used in a portal. But labeling the TO as someThing_someThingPortal hides actual relational concepts.

Test File DemoJOBR.fmp12

Great suggestion, Bruce. I had named them "portal" which made it easier for me to understand but your terminology is the correct way. Also nice and tidy.

Thank you.

Edited February 3, 2018 by Christoph

[Christoph]

Hi Josh,

I'm really enjoying the new additions to the file and I have to say it works really nicely

Is there a way to incorporate these 2 features. The first is when you add a New TV show or Film, it immediately gets highlighted and the detail right side is blank? As it stands, I have to click on the new portal row to highlight. The second is, when adding a New TV show or film, it ends up at the bottom and I would like it to end up on top of the list.

Thank you!

[Josh Ormond]

1. You can add a script trigger to field where you add a new line. Have it set the global fields to the values that connect the relationship the way you want. It may take some playing around with the different functions to get the IDs of the records you need.

2. Add an auto-enter timestamp field to the related table. Sort the portal on that field, descending order.

[Christoph]

8 hours ago, Josh Ormond said:

1. You can add a script trigger to field where you add a new line. Have it set the global fields to the values that connect the relationship the way you want. It may take some playing around with the different functions to get the IDs of the records you need.

2. Add an auto-enter timestamp field to the related table. Sort the portal on that field, descending order.

Arrgh! Lol. I'm not able to make either of these work but I won't give up. In your description, it all seems so simple and yet, I seem to fall short. As always, Josh. Thanks for posting  

[Josh Ormond]

• Added fields to log creation timestamp.
• Added the sort to the portal based on the creation timestamp.
• Added object name to the portal, so we can use it to jump to it in the script.
• Create a new script to use OnObjectSave.

Test File DemoJOBR.fmp12

[Christoph]

16 minutes ago, Josh Ormond said:

• Added fields to log creation timestamp.
• Added the sort to the portal based on the creation timestamp.
• Added object name to the portal, so we can use it to jump to it in the script.
• Create a new script to use OnObjectSave.

Test File DemoJOBR.fmp12

Josh! What can I say... thank you so much!! You have taught me so much in the last week. I'm doing the happy dance again  

I've studied how you have things laid out and now I understand where I went wrong. I would never have figured out the OnObjectSave. Excellent work!

Thanks again!

Chris

[Josh Ormond]

I know seeing examples was one of the best ways for me to learn. Just happy to return the favor.

[Christoph]

5 minutes ago, Josh Ormond said:

I know seeing examples was one of the best ways for me to learn. Just happy to return the favor.

You're 100% right. I've learnt more from your examples and your work than I have from anyone else. For me, these examples files do the trick 

Cheers & thx! 

[Christoph]

Hi Josh,

Been trying to figure this one out by researching but I may not be googling the right questions. For reference, lets take latest example file in this thread, which is the most recent.

When I open the file, it automatically opens up with the Song Info layout. This is good. I then go to the TV & Film Pitch layout, click the bottom entry "Big Show 2", Quit FileMaker and open the file up again... From Song Info, go to TV & File Pitch... how can I make the top and latest entry, in this case, "Foggy Nights' be active and highlighted in red? I'd like the latest entry to always be the one I see showing on this page,  when going to this layout. I know it's a Script Trigger and possibly a Conditional Formatting but this is where I'm at a loss.

Thanks!

[Josh Ormond]

The formatting is already handled by the Highlight script you have. Actually, the process you want is already in place. The "OnRecordSave - Highlight Created Portal Row" script does what you want. Currently, the script trigger is on the entry field with an OnRecordSave trigger.

You can use that same script to select and set the record as the active record on the layout. Nothing will be created, because there is nothing saved in that creation portal.

So now you need to figure out when you want that trigger to run.

1. OnLayoutLoad
2. Only the first time the layout loads.
3. Something else.

For #1, that get get tricky if you have any scripts that leave the layout and come back. So I wouldn't advise that one.

For #2:

• use the OnFirstWindowOpen trigger to set a global variable.
• In your script to go to the TV Pitch layout, test for that variable. If it exists, run that OnRecordSave script. It will do what you want.
• Then clear that variable. The next time your script to go to the TV Pitch layout, that variable won't be there, and it won't select the newest record every time.

 

[Christoph]

Once again I feel like a dummy. Yes, you're right. I do need to figure out when I want the trigger to run and #2 (Only the first time the layout loads) seems right. When I follow your instructions, I wouldn't even begin to know where to add the info into the script OnFirstWindowOpen and setting a global variable. I tried all night to figure this out and made a mess of things, although your explanation seems rather easy. If you have the time, is there a simpler way to explain this? If you don't Josh, I totally understand. Thank you.