5 Replies Latest reply on Feb 2, 2010 4:49 PM by njem

    How to do this form?



      How to do this form?


      I'm changing a db from access to fm and need to know the best way to do a piece.


      A db of bowling teams for a non-profit fundraiser. There's a teams table and form. The primary key is a combination of captain's last & first name, company name, and team number (the captain might head multiple teams). There's a schedule table and form that has a primary key of bowling alley name, lane number and date/time. To make it easy to link, when a team is made an auto-serial number is assigned. The schedule has a field for the same. It's a 1-to-1 relationship. I used the serial number for ease of design. It could literally link on captain name/company/datetime.


      When viewing a given team you can click a button and bring up the schedule form. This is a list of slots (alley, lane, date/time). Several hundred of them. The whole list displays and the scroll bar lets you scan through it quickly. (One nice thing about access, as you move the scroll, the display moves so you don't have to let go to see how far you've scrolled, or you don't have to page down in chunks.)


      If this team has already been scheduled the form jumps to that position, which is important. There are buttons to show all slots or just open slots, and buttons to sort by date/time or sort by location. (When you have someone on the phone and you're trying to say "how about this location at this time? No? How about that location at that time?" you want to scan and see what's available quickly.)


      When you want to assign a team to a slot you pull down a list that shows captain name/company/team and chose yours. It doesn't have to be this particular method of assignment, just what was user friendly. It could be that you click a button and it fills in with whatever team you left open back on the teams form. That's a little less convenient because sometimes they're shuffling people and going up and down in the schedule moving this one to this slot and that one to that slot. To unschedule or change schedule you delete the name in the captain field, then find another slot and assign them again.


      When you close the schedule and are back at the teams form the alley and date/time show on the team form.


      This is mostly going to be accessed through Instant Web publishing.


      Since both FM's list and even table is not as quick to work with, and web viewing limits to a list of 25 or a table of 50 and then you have to walk through page by page, I was thinking of this design for the schedule: A radio box to select showing this alley or that alley or both alleys. There are only 5 or 6 days for this event so a radio box to select showing only day 1,2,3,4,5 or all. If you select a day and alley you only have 36 lanes so (in list view) two pages to flip between, which is manageable. One catch to this is I don't see how to put several controls on a list view.


      An alternative would be to make each line contain the alley name, date/time, and then 36 checkboxes all on the line indicating if lanes are scheduled or open (or maybe a count of open lanes?). That way you could look at a lot of slots at once and see if there are openings. If so then there would need to be a way to open another form with a detailed view of that slot that shows who is scheduled in each lane and add your team to an open one.


      I'm betting some folks who do FM design a lot know exactly how they would do it and could save me a lot of going down the wrong path for what works well in FM.



        • 1. Re: How to do this form?



          Thank you for your post.


          Designing a database can take an inordinate amount of time, and it sounds like you already know what you want to do.  At this point, it may be easier to just get started, and then post any specific problems.  Other forum users will then be more likely to respond.



          FileMaker, Inc. 

          • 2. Re: How to do this form?
               Already know what I want to do? Not really. I was just describing the way it worked in Access. Given that a list of a thousand long time slots as one long table is not as convenient in FM (a rare negative) and not possible at all in web view (except as 25 or maybe 50 at a time an not real quick switching between pages) it needs a different structure. Even my own suggestion, of a summary report that lists day and hour slots and then gives a summary number of how many lanes are open at that time I can only get to work within FM, not web view. Haven't figured out a way around that at all. So while I've done a lot of stuff around the edges and made several passes at the scheduling form I'm stuck. I did manage to get the web view to show a summary field if it is in a footer, but then you only have the summary value to the first break field, not a list of subsummary values. There may be some trick for a field in the table to maintain a count of occupied time slots. I've tried that but haven't succeeded. Making the mental transition from access, in access you have a pretty flexible query tool and can then base a form on the data that results. Maybe there's a way to do similar which would contain a count per break field. Don't know. It's this kind of thing I was hoping someone who's done a lot would know how to avoid the false starts and state how they would do it.
            • 3. Re: How to do this form?

              Just brainstorming this, I suppose the proper relationship between my teams table and my schedule table would be a "join" table, an intermediate table. The team table has all its info and the team number. The schedule table has all the alley and dates and lanes. The join table would have just the team number field and the key fields of the schedule and would have relationships to each of the other tables. Not sure if that makes anything any easier, but maybe opens some possibilities?

              • 4. Re: How to do this form?
                   In both access and filemaker Join tables are used to implement a "many to many" relationship. The key question here: Do you have many "Team" records that have to link to many "Schedule" records? That appears to be the case since your schedule table probably stores something like 1 record per schedule "day" and a given team would be scheduled for a whole series of such days. If so, a join table makes good sense here.
                • 5. Re: How to do this form?

                  You're right. My mistake. I was thinking about a separate issue I don't want to confuse this thread with because I was hoping the thread would give me some insight on form design. So just by way of explanation the list of time slots needs to remain even if a team that was scheduled is now deleted. I don't want a cascading delete to get rid of the slot altogether, it's just that the slot should now have a blank team number, that is it's available. I was thinking of my table of teams, make the table of time slots just that, while an intermediate table serves to link the two. That way if a team is deleted the associated link record can also be deleted, but that actual time slot in the schedule table still exists. I think I may still do something like that, though that has nothing to do with general form desgin in FM.