Displaying multiple records accross the page.

[Mark Jones]

I love Filemaker and have been using it for years in our Church Boyscout Group called Royal Rangers. You all have helped me from time to time to figure stuff out.  I am a phoneman for at&t and by no means a programer.  My databases are basic and not fancy.

 

I have been printing out 400 certificates for the boys for their merit work.  It also fills out the order forms and prints off the lists for our Awards Ceremonies.    Last week a guy spent 6 hours using power point to display 200 merits for about 60 boys.  It was  a hit.  I thought well I have the data.  I just need to figure out how to put mulitiple merits accross the screen.

 

Certificates are easy. All one record I am just displaying that info.

 

The rub.  How to get 4 to 8 container fields to display.  Like this graphic shows.

 

Any suggestions? 

 

I use Filemaker almost every day and have for years and years. It's a great program and will upgrade to 12 soon.  This is new ground for me.  If I pull one boys record I can display the merits he has with a sorted summary and  can also do that with the label option accross the page.   I can script that but it seems to be clunky.  I know you guys and gals have a idea that will put me in the right direction.  It would be cool to have it do it on the found records on a full name sort. 

 

It's been a year or two since I did any real changes to the relational 35 databases.  My brain needs a jump.

 

 

Mark Jones

 

 

 

 

.

[doughemi]

Create a Badges table of all the merit badges with a description field and a container field containing the graphic.  

Create a return-delimited list field in each boy's record containing all his awards. This could be a calculation field List(BadgesAwarded) if you have such a related table.  

Create a relationship Boys::Awards = Badges::description

Add a series of portals to Badges on a Boys layout, each one a one-line portal with the next sequential Initial row value.  Place the Badges::graphic field in each portal.

[Mark Jones]

Hi Doug. The return delimited list field only lists the one badge the boy has earned.  Not all of the bages he has earned.  The portals I haven't used before as you can tell I am green on this part of it.  I assume we can't use a portal from the same table.  If I could see an example of how it's done I could most likely get it to work in my data base.  My data files have over 4000 records in some of them so it's hard to just put them up with out taking all the data out of them. 

 

Could you put these 3 lines in a file for me look at.  I appreciate your help.  I have been trying to script this all out and it's given me a headache..GRIN!  I know this is simple stuff for many of you.  It's been a while since I did any real changes in Filemaker and I have had to relearn some things I have forgotten.  Had to go back and look at old programing to see how I did it.  GRIN!  Thanks for any help here.  I know the Boys will enjoy seeing their merits and faces  up on the big screen when we do our Council of Achievements.  120 boys with 400 merits one can see why it would be nice if this is dynamicly created. 

Create a Badges table of all the merit badges with a description field and a container field containing the graphic.  

Create a return-delimited list field in each boy's record containing all his awards. This could be a calculation field List(BadgesAwarded) if you have such a related table.  

Create a relationship Boys::Awards = Badges::description

Add a series of portals to Badges on a Boys layout, each one a one-line portal with the next sequential Initial row value.  Place the Badges::graphic field in each portal.

[doughemi]

HTH.

 

The design has changed slightly because I didn't originally catch that you wanted to display awards from a certain time frame, not accumulative total.

 

 

badges.fmp12.zip

[Mark Jones]

Thanks Doug.  Wil check it out and see if I can add this to my database.   I watched a video on Youtube this morning on Portals, Table occurances.  I have not used either of these.  My skills are very limited yet Filemaker is handy  enough even I can  get things done.   I saw things on that Youtube video that I didn't even know Filemaker could do.

 

Did you happen to notice the layout design on related container fields?   If you click on them like you do the rest of the layout fields. It may or may not come up with handles to select it or move it or delete it.  This is a new anoyance since I upgraded this last week.   The RRadvane.fmp12 file look at the first graphic on the far left in the Body Layout next to the labels gray boxes.  You can click on it a bunch of times and sometimes you can get it sometimes you can't.  It is the same as in the other 33 relational database I have.  I did notice the contianer field to the right of the full name just below can be grabbed with no data in it. 

 

it's like the program is looking for something.  It's doing the same thing on my laptop and my desktop.  Just the layouts with relational container fields.

Mark Jones

[Mark Jones]

Hey that works slick. I added 4 more portals and saw how that works. It makes a whole lot more sense when it's there to poke at.

 

Mark Jones

[Mark Jones]

I added 4 more portals so 8 merits would display.  Then I had a boy with 14 merits.  So I am back at the original layout.  With the labels layout option.

 

I have a script that finds the boys records per a date range. I did pickup a few tips from your example work and put them to work in my datafiles. THANKS!   I am not a programer so these things come hard for me.

 

I Know I did this in a clunky way but it did what I wanted. 

I made a script that takes the found set of merits and copies and pastes the Age Group name (13 Groups possible) from a value list.  Then adds the Boys full name, then the merit name. Adds a carriage return so the next record goes down one line.  It all goes into a text field.

I then made it loop through that and put that data in a text field. The problem I have with that is all I want is the one boys name and have a script that finds that boys records between a date range and then prints it to the pdf factory that will allow me to print 100 boys merits to one pdf file for a power point type display. 

 

I can do it manually one boys record at a time.   It would be cool to automate it.  This is what I have so far with a script that asks me for the boys name. It uses a date range field.

http://readyrangers.tzo.com/marko/powerpoint2013.pdf  the file is hi resolution and 9 meg insize.  It has 4 boys records in it. 

 

I am getting closer but I know there is a  better way?   Ideas anyone? 

I will get the new files posted tonight.  I know it's hard to figure out stuff you can't see first hand.

Posted now.

 

http://readyrangers.tzo.com/markoRRangers.fmp12

http://readyrangers.tzo.com/markoRRadvanc.fmp12

http://readyrangers.tzo.com/markogph.fmp12

 

 

Mark Jones

[Mark Jones]

_{I appreciate the help.  This is a video of what I am trying to automate.  Ideas on how to  do this?}

_{http://www.youtube.com/watch?v=cxpUV_pIS1Y}

[LaRetta]

Hey, I've been really swamped but few days ago I saw this thread and didn't think anyone had responded so I created a demo file and then I saw Doug had posted and was on it, so I didn't continue particularly when I read that the scope changed and I wanted to think about it some more ... anyway, here is how I would turn related records sideways (attached 12 file).

 

This method allows entry of a date range into the yellow global fields and calculation displays the badges in repetitions using:

Let ( i = Get ( CalculationRepetitionNumber ) ;
Case (
i  ≤ Count ( ScoutBadgeDisplay::MeritID ) ;
GetNthRecord ( Extend ( ScoutBadgeDisplay::MyBadge ) ; i ) ; "" )
)

NOTE:  This would not be a fast calculation but it should be very limited using the date range to only a Scout's badges but YMMV.  And since this is just to find all Scouts who had Merits in a date range and then display the Scouts in a Scouts list with their Merits, it should work quite fine.  And my apology if I am stepping into something I shouldn't but I liked playing with the badges too and I had already done most of it, so ...

 

Anyway, form and list views below. 

Merits.zip

[LaRetta]

Also, I know you wanted browse mode but another possible option is to use labels, with fill across first. 

[Mark Jones]

That file you downloaded yesterday where i was having the layout handle issue that you told me to change the fill to non transparent. That layout is a label layout. 

 

I have it working for one boy at a time where it finds the boys merits by date range then prints it out to a pdf file. 

 

I would like to do this automaticly.  Can you look at that RRadvane.fmp12 file and see what I am trying to do.

 

Much appreciated. 

 

Mark Jones

[LaRetta]

You have your entities mixed together in flat file.  Have you looked at my file?  I am too busy right now, sorry.  If you can use what I presented fine but if not please continue as if I wasn't here. :^)

[Mark Jones]

Thanks LaRetta.  I can make it work with the flat file labels layout for now.  I just have to manually enter the boys name and have the script continue. It's flat but it works.  GRIN!

 

I did look at both Doug's and your sample files.  The relationships and tables are interesting and I have not really done any thing above Flat database work.  I make scripts that cut and paste the info into the data files.  It's clunky but it works.  I am able to track all kinds of stuff  using the flat database system  most of my data base files only have one table in them.  I am not a programer that is for sure.  If your Broadband  is broken..I can find and fix any fault..but programing... does not come easy. 

 

You and Dougs simple database files with these merits are way above my level of programing.  Yet Filemaker allows me to do way above the rest of the database programs I have seen and I love the power and simplisity of the program.

 

Mark Jones

[LaRetta]

Well, you asked for help on accomplishing it and we gave you our best suggestions and the suggestions we make are not just because we are programmers but because they are the most efficient and flexible which you are finding out your structure lacks with this very issue.  I commend you for doing so well with it and I hope we can help you in future with other things besides structure.

 

But if you expect us not to recommend structure change  each time you post a question then you had better mention that in your opening sentence so we don't bother trying to show you a better way; that is, after all, our jobs.  As is, I am sure you will figure this this one out just fine. And next time I'll try to remember that you are someone who is happy where they are relationally so that you do not feel you must defend your decisions ... I do not want you to feel that way.  So please make sure you mention it in your opening sentence on posts in case I forget. 

 

And nice badges!!  I can see why the kids get excited about them.

[Mark Jones]

In the portal layout how would you address a boys merits that had 30 merits in one awards ceremony?  The label layout seems to over come the limitations of the advanced relational tables.  I am not stuck in my ways it's just sometimes the new ways are not as flexible or most likely I don't see the answer. GRIN!

 

Can the 8 layout portal container fields  be extended to unlimited amount of portals?  So if the boy has more than 8 merits they still display on the next page?  That would fix the delima. 

 

My abilities in programing are not any where close to the vast majority here.  Yet if there is a way I can generally stumble across it. GRIN!   

 

I do appreciate the time you and Doug have put into the portal setup.  I have already learned some  things from them. 

 

Mark Jones

[Mark Jones]

For  20 years with the Flat file format I have been able to export and import records with ease and no loss of data.   I tried exporting records into the relational badges files.  With out using the Full name as the "ID" the data is a mess. 

 

I get folks send me excell records that have been easily imported into the records. Filemaker does a excellent job of this.  The relational lookups while I see they have their place also have a major draw back in the design as well.  Not so easy to work with or get data out of them.  The Flat file system using Full Names as the ID with relationships works better in this case.  Yes in the place of duplicate full names.  That could be a issue but it rarely happens in my 2000 folks to track.

 

I did get the badges files to go with mulitiple portals even showing 20 records but the label layout using a find is working the best for me at this time.  I am still playing with the badges files and may modify them to use full names so they will import and export data where needed.  Even the badges work better using the full names lookup.

 

Mark Jones

[LaRetta]

"The Flat file system using Full Names as the ID with relationships works better in this case. "

Baloney. It is because you do not know what you are doing. A tin can and a string seems superior to someone who does not understand electricity.

You can come back to this post and continue to disagree all you wish but truth is truth. For you to think you can turn database developers ( which you obviously are not ) into switching to flat-file then you will be waiting a long time indeed. I suggest instead that you open your mind and listen and learn while here, instead of entrenching yourself in erroneous beliefs.

[Mark Jones]

Wow!  I see what the world is going to.   I saw some of this while reading other posts around here.  Seems the enlightend no longer have room for us that have said from the git go we are not programers and your methods are way above our methods.  YET keep in mind our methods do work and have worked well for over 20 years.  So now that we have established the fact again that I am no programer but I do use Filemaker since 3.0 to 12 and have spent several thousand dollars so I can give out for free over 35 solutions that do work and are a blessing to folk and yes your right I have not engaged in Record ID's and relational tables that are locked in by a id.  YET I do get along fine with the Full Name ID relational for my purposes. 

 

I am seeing the dissadvantages of the ID number versus the full name.  Import and export is a major one.  I did examine yours and Dougs badges files and they did do what I had asked for but they did a lot less than what I already had and was more of a hinderence (because of my limited programing skills as we all know) so I stayed with what works.  I take what works for "me" and put it to work even if it's a flat database system. 

 

I used to think I could come here and get some questions answered.  Only a few times have I come here to ask.  Yet in the past I never had this kind of attitude to deal with.  Kind of like feeding a dog that bites.  Will be more careful next time or take it underground. There are folks that still like to help and over look the fact that most don't know as much as they do.     

 

I do appreciate the help and your indulgence to discuss these things.  Just please keep a open mind.

 

Mark Jones

Also keep in mind.  I daily visit folks business and residences that have had weeks of poor or no broadband services.  Realizing my niche in this is I can fix it where others fail badly at it does not give me the right to talk down to them. It's just not their expertise.  So even if they are cranky.. I help if I can. 

 

Mark Jones

[Rick Whitelaw]

Mark,

Reading through this thread it seems to me some very talented and knowledgable people are giving generously of their time to give you a hand. I don't notice anyone talking down to you. If what you have works for you that's great, but you WILL be limited as you try to accomplish certain things.

People are simply giving you their best advice.

Rick.

[LaRetta]

Only a few times have I come here to ask.  Yet in the past I never had this kind of attitude to deal with.  Kind of like feeding a dog that bites.  Will be more careful next time or take it underground. There are folks that still like to help and over look the fact that most don't know as much as they do.    

 

Wow.  You are the one who re-opened this thread, Mark.  Why? To convince everyone flat-file is better and that keys should be names?

 

Hey, I do not care whether you use proper relational structure or not ... really I don't and I'm not offended in the least that you are offended in my attempts to assist you, because I understand that hearing the truth can be painful.

 

But flat-file design is limiting and will frequently dead-end.  Using names as primary keys will break.  And I have every right to make that statement because this is a public forum and it is the truth according to my 18 years in database management full time, and the truth according to everyone in the database management industry except you.  I say it again now for the record so others who are newer to this field don't head down the wrong path.

 

I have several clients with flat-file structures which currently can't be changed - I don't fault you for having flat-file structure at all and I understand how someone can get into that position.  You ask me to keep an open mind?  Mark, I see no question from you as to how we would import and surprisingly, thousands of Developers import every single day easily with properly normalized structures.

 

Maybe some day you'll be willing to consider alternatives.  As for me, I have lost the desire to assist you further because a man convinced against his will is of the same opinion still.  Good luck with your project and I mean that.

[Mark Jones]

Thanks for your help.  It may not have been intended but I did come away with a less than a properly normalized response.   No biggie.  Just caught me off guard.

Sorry for any ill will I may have caused.

 

Mark Jones

[mr_vodka]

For  20 years with the Flat file format I have been able to export and import records with ease and no loss of data.   I tried exporting records into the relational badges files.  With out using the Full name as the "ID" the data is a mess. 

 

I get folks send me excell records that have been easily imported into the records. Filemaker does a excellent job of this.  The relational lookups while I see they have their place also have a major draw back in the design as well.  Not so easy to work with or get data out of them.  The Flat file system using Full Names as the ID with relationships works better in this case.  Yes in the place of duplicate full names.  That could be a issue but it rarely happens in my 2000 folks to track.

 

I did get the badges files to go with mulitiple portals even showing 20 records but the label layout using a find is working the best for me at this time.  I am still playing with the badges files and may modify them to use full names so they will import and export data where needed.  Even the badges work better using the full names lookup.

 

Mark Jones

 

Mark, I can see why you would think that. Sure if may be easier to import records into a flat table directly rather than splitting it off into multiple tables, but the advantages of having a properly set up relational model far outweighs the few pluses of a flat table.

 

What you can do though is import your excel files into a temporary flat table and then run a script routine to put each row into the proper relational tables that you can use for your solution.

[Mark Jones]

Thanks guys and gals.  What I have working now is way ahead of where I was a month ago.  There is always a better way but often there is many ways to get something done. 

 

I did pickup some great tips from the files.  Even changed several things that worked great.  The date range was a great help.  I have been updating several of my file that use date ranges. 

 

I keep plugging away in my ole set ways.  In 34 years at at&t one doesn't stay set in their ways long.  Technology changes..but I have learned that somethings just work better for me and I use those ways.  NOT TO SAY I won't use the record ID's as I have already started my main files with them..but it's a new way and putting it to work for me in my database files may take some time.  Yet I will adapt. . So Larreta..don't write me off.  I may apprear to be full of baloney and not smart enough and that is quite often true....yet I keep after it.  Give it some time.  You may grow to smurk and then grin when I ask a really stupid question.  Yet look out.. I out preform most folks. 

 

 

 

Mark Jones

[LaRetta]

I may apprear to be full of baloney and not smart enough and that is quite often true....yet I keep after it.  Give it some time.  You may grow to smurk and then grin when I ask a really stupid question.  Yet look out.. I out preform most folks. 

 

Hey Mark, I did NOT say you were full of baloney ... I said one of your statements was baloney (and I stand by that) so please do not turn what I say into more than I said.  Neither did I say nor imply you were stupid and I do NOT smirk or grin at anyone; it isn't my style and I do NOT make fun of folks that know less than I do; in fact newer folks teach me things every single day which is one of the reasons I love coming here.  Forum guidelines clearly states that if you have a thin skin you might not want to post on any forums.

 

If you come to this forum from the perspective that folks are making fun of you and smirking and grinning at your questions and thinking you are stupid then I will not be the only one you will bump up against; I am just the first ... wait until you meet Bruce ;-) and lucky for you Michael isn't around.  For others who assist you, please realize that we all donate our time (which we normally get paid for) to help folks - just like I was helped years ago when I too went through exactly what you are going through (and still go through sometimes).  So relax ... this is a great group of folks whose only goal is to provide the BEST answer they can provide ... which many times is not what the OP wants to hear.  Such is life.