Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 9 of 9
  1. #1
    Gold Member
    Join Date
    Jul 2010
    Posts
    90
    Location
    Uniontown, Pa. USA
    FileMaker Pro Software
    Filemaker Pro Advanced 14
    OS
    Windows
    Skill Level
    Novice
    Last Post
    03-22-2017
    Local Date
    11-23-2017
    Local Time
    04:30 PM
    Last Active
    03-25-2017

    Basic design advice and help

    I have a question on how to best accomplish the following. I have a form that I'd like to make a database for. The form has the following needs and I can't seem to wrap my head around the way to do it.
    I need all of this to fit on one page - landscape or portrait is unimportant. I have an instructor visit my dojo. We have a paper form to fill out each time they visit and then we submit it to our parent organization. The form has to cover from one to 5 days of his visit. That means every day has a DATE, LOCATION of the event, TESTING FEE, INSTRUCTION FEE, PER DIEM FEE, OTHER COST, and OTHER COST DESCRIPTION. These items have to TOTAL daily and then GRAND TOTAL weekly. All of this needs to be one record overall per visit. There are a few other fields needed but they will be text fields. My first thought was an invoice type of solution but I couldn't understand how I'd have different days (drop down calendar?) unless I'd have separate portals and that baffles me. Somewhere in the back of my mind it's nagging me about a JOIN TABLE but I wouldn't have a clue how to go about this.
    I hope I'm being clear but I suspect I'm leaving something out.
    Can I get anyone to help guide and advise me?
    Thanks,
    Jim

  2. #2
    Senior Moderator - FMT Preferred Trainer - Consultant: Australia, New Zealand, Japan & Pacific Rim David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,893
    Location
    Sydney, NSW Australia
    FileMaker Pro Software
    FileMaker Pro 15 Advanced
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    10-23-2017
    Local Date
    11-24-2017
    Local Time
    07:30 AM
    Last Active
    11-01-2017
    My first input would be that the paper form is not necessarily the best fit for an online format. You can do so much better with online.

    So you need to look at what data you need to collect. You might need to collect more data on the first day, and then there is supplementary data each other day until the end when there may be extra data.

    Certain data will probably be the same for every day -- LOCATION of the event, TESTING FEE, INSTRUCTION FEE, PER DIEM FEE. but each day there will be data that uses this data as multipliers -- number of tests, number of attendees.

    While there will be one record for the 'visit', that record will have child records such as 'day' which all contribute to
    the visit totals. Each day, the visit record is retrieved and added to.

    This architecture planning is the sort of thing I do for people who want to build their own solutions.
    Regards,
    David Head
    FileMaker 15 Certified Developer
    FBA Trainer
    LinkedIn Profile

    There is always a well-known solution to every human problem - neat, plausible, and wrong.
    H. L. Mencken, Prejudices: Second Series, 1920

  3. #3
    Gold Member
    Join Date
    Jul 2010
    Posts
    90
    Location
    Uniontown, Pa. USA
    FileMaker Pro Software
    Filemaker Pro Advanced 14
    OS
    Windows
    Skill Level
    Novice
    Last Post
    03-22-2017
    Local Date
    11-23-2017
    Local Time
    04:30 PM
    Last Active
    03-25-2017
    David,
    You are correct in saying the paper form isn't the best but our org wants us to fill it out and send it in. They are big on paper records as a fall back. The records will change for each day in so much as the amount of any one thing. The date for that day change. The amount of students testing will change as will the type of test. Color belt testing or black belt testing. Hours of instruction will change each day or could. The number of just attendees isn't a factor. The per diem will be a quantity also but most likely could be hard coded. Having all of this as a weekly record with child records is what is stumping me the most. I can create a basic invoice type of layout that gives me all of this flexibility but only would cover one day. I realize the input layout could and should be independent of the printing layout but that doesn't help me much. Is this even possible? Or should we just continue using a blank sheet and doing the math by hand?
    I appreciate your help.
    Thank you,
    Jim
    Last edited by James56; 03-19-2017 at 07:10 PM.

  4. #4
    Senior Moderator - FMT Preferred Trainer - Consultant: Australia, New Zealand, Japan & Pacific Rim David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,893
    Location
    Sydney, NSW Australia
    FileMaker Pro Software
    FileMaker Pro 15 Advanced
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    10-23-2017
    Local Date
    11-24-2017
    Local Time
    07:30 AM
    Last Active
    11-01-2017
    I definitely think this can be done. You just need to get the structure right. And then look at a simple UI to run the process. The input will definitely be different to the printing layout.

    Using a blank sheet and doing the math by hand leads to multiple data entry, math errors, other errors, omissions and more.

    I can't help much more without more details -- otherwise we just go back and forth until I discover all the details. So where you could start is to outline a typical day in the life of an instructor noting the data they currently fill out on the paper form.
    Regards,
    David Head
    FileMaker 15 Certified Developer
    FBA Trainer
    LinkedIn Profile

    There is always a well-known solution to every human problem - neat, plausible, and wrong.
    H. L. Mencken, Prejudices: Second Series, 1920

  5. #5
    Gold Member
    Join Date
    Jul 2010
    Posts
    90
    Location
    Uniontown, Pa. USA
    FileMaker Pro Software
    Filemaker Pro Advanced 14
    OS
    Windows
    Skill Level
    Novice
    Last Post
    03-22-2017
    Local Date
    11-23-2017
    Local Time
    04:30 PM
    Last Active
    03-25-2017
    David,
    I've attached a mockup of the form. At ;east I think I did!
    Thanks,
    Jim

  6. #6
    Senior Moderator - FMT Preferred Trainer - Consultant: Australia, New Zealand, Japan & Pacific Rim David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,893
    Location
    Sydney, NSW Australia
    FileMaker Pro Software
    FileMaker Pro 15 Advanced
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    10-23-2017
    Local Date
    11-24-2017
    Local Time
    07:30 AM
    Last Active
    11-01-2017
    OK that is very useful.

    From that form data, you would need to set up tables for Club, Instructor, Visit, Day and Cost.

    Club -- name, logo, country, region
    Instructor -- first name, last name, address
    Visit -- event name, ID instructor, start date, end date, fee rates (?), grand total costs, signatures
    Day -- ID visit, date, location, total costs
    Cost -- ID day, category, quantity, rate, total

    The basic ERD is:

    CLUB ---< VISIT ---< DAY ---< COST

    INSTRUCTOR ---< VISIT

    In terms of UI, you would set up a visit will all the data including specifying the instructor and even the required day records.
    Each day, the instructor would complete the day data (which automatically feeds back to the visit.
    At the end of the visit, the visit record would be signed off by instructor and host.

    That should get you started.
    Regards,
    David Head
    FileMaker 15 Certified Developer
    FBA Trainer
    LinkedIn Profile

    There is always a well-known solution to every human problem - neat, plausible, and wrong.
    H. L. Mencken, Prejudices: Second Series, 1920

  7. #7
    Gold Member
    Join Date
    Jul 2010
    Posts
    90
    Location
    Uniontown, Pa. USA
    FileMaker Pro Software
    Filemaker Pro Advanced 14
    OS
    Windows
    Skill Level
    Novice
    Last Post
    03-22-2017
    Local Date
    11-23-2017
    Local Time
    04:30 PM
    Last Active
    03-25-2017
    David,
    I posted a reply asking a few questions and it said my reply had to be approved. Did I do something wrong? I'm editing this as it seems I did do something wrong but I don't know what. I might have somehow started a new thread? I don't know.
    Anyway here's what I asked in the misfire -

    I didn't start on the solution yet. I want to understand some more things first. Also,there isn't a need for signature as that's what the instructor will do on the printed copies. Also, the logo will only be the parent org and can be hard coded in. I know each table has to have it's own unique Id. It will connect to the other table(s) with the primary key/foreign key setup. With that said I want to know if this is correct:

    ClubIdPk = ClubIdFk in the VISIT table?
    VisitIdPk = VisitIdFk in the DAY table?
    DayIdPk = DayIdFk in the COST table?
    InstructorIdPk = InstructorIdFk in the VISIT table? And should this (VISIT) actually be a TO? Or just connect it to VISIT ?

    I haven't been able to understand the last part but I believe you're saying that the club rep would fill out the event parameters and the instructor would fill in the end result (hours and such) after the event. Actually the instructor won't be doing anything except signing off after the event on the printed copy. I give them one, keep one, and send one to my parent org. for there records and dispersal of the actual money I send them.
    Hope this makes a bit more sense and I apologize for whatever happened to my first attempt to reply!
    Thanks,
    Jim
    Last edited by James56; 03-21-2017 at 04:14 PM.

  8. #8
    Senior Moderator - FMT Preferred Trainer - Consultant: Australia, New Zealand, Japan & Pacific Rim David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,893
    Location
    Sydney, NSW Australia
    FileMaker Pro Software
    FileMaker Pro 15 Advanced
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    10-23-2017
    Local Date
    11-24-2017
    Local Time
    07:30 AM
    Last Active
    11-01-2017
    Hi Jim
    My naming convention is that each table will have a primary key called ID; any table requiring a foreign key will have "ID name".

    So you are correct in where to place the foreign keys.
    All the TOs can be joined so, yes, join the Instructor TO to the Visit TO.

    For the last part, it really depends on how you intend using the solution you are building. I envisage it as an iPad app to be used in the club. Then the signature capture would be entirely appropriate and quite cool. YMMV and I don't know what you intend there.
    Regards,
    David Head
    FileMaker 15 Certified Developer
    FBA Trainer
    LinkedIn Profile

    There is always a well-known solution to every human problem - neat, plausible, and wrong.
    H. L. Mencken, Prejudices: Second Series, 1920

  9. #9
    Gold Member
    Join Date
    Jul 2010
    Posts
    90
    Location
    Uniontown, Pa. USA
    FileMaker Pro Software
    Filemaker Pro Advanced 14
    OS
    Windows
    Skill Level
    Novice
    Last Post
    03-22-2017
    Local Date
    11-23-2017
    Local Time
    04:30 PM
    Last Active
    03-25-2017
    David,
    It did it again! I just wrote a reply and hit submit reply and it disappeared with the note about reviewing my post!
    So here goes again.
    I did make the tables using table name and IdPk/IdFk. It just is easier for me to understand. I joined them and even filled in some data but now I don't know where to go from here. I suspect I need to create the form and populate it with portals?
    The form has to be similar to the one I showed you. It can be changed to landscape and some of the fields (Day?Location) can be separated. By the way DAY actually refers to the DATE. It's explained under the heading for this item. But I have to be able to fill it out and print 3 copies. One for me, one for HQ, and one for the instructor. HQ disburses the money that I send to them. We don't pay the visiting instructor. ipads and the cloud would certainly make sense (in todays world) but ours is an ancient art and org and change doesn't come easy or quick. I have gotten them to use computers but it's taken years and they almost always make written backups of everything. I don't know if this is unique to them or not but it's what I have to deal with. I run mu dojo (club) entirely on the computer. Jean Wenmeekers has helped me tons of times throughout the various manifestations of what I pass off as a solution. He must cringe when he see's an email from me! But I owe him a lot and really appreciate all of his help and patience. Especially his patience : )

    Guess I'll see what I can figure out.
    Thanks,
    Jim

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need advice on database design
    By ScottJ in forum FileMaker Pro 11
    Replies: 3
    Last Post: 07-22-2010, 02:34 PM
  2. Help with basic design question
    By gregtuite in forum Portal & Relationships
    Replies: 3
    Last Post: 02-26-2009, 09:49 PM
  3. Database design advice
    By Raven in forum FileMaker Pro 8.5
    Replies: 5
    Last Post: 09-05-2007, 01:33 AM
  4. Basic Design - have I gone wrong?
    By sallymartin in forum Portal & Relationships
    Replies: 7
    Last Post: 11-15-2006, 09:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.