Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

Recommended Posts

James56

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

Share this post


Link to post
Share on other sites
David Head

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.

Share this post


Link to post
Share on other sites
James56

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

Edited by James56

Share this post


Link to post
Share on other sites
David Head

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.

Share this post


Link to post
Share on other sites
James56

David,

I've attached a mockup of the form. At ;east I think I did!

Thanks,

Jim

Share this post


Link to post
Share on other sites
David Head

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 ---

 

INSTRUCTOR ---

 

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.

Share this post


Link to post
Share on other sites
James56

David,

Thanks for the direction. I want to get this defined a bit more before I start making the actual file. Each table requires an ID I know. Primary key and Foreign key. In the relationship diagram am I correct in that the

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 ?

 

Also - I don't think a SIGNATURE field would be needed as this only refers to an Instructor and Club Director actually signing off on the printed form once all is said and done. If I'm getting something wrong please let me know. Also, the logo will be hard coded as our parent org not the individual clubs. That only needs to be on the finished form.

Lastly, I don't understand the last part much but I think it will become clearer as the project progresses. I'm thinking I'd fill out the basic data for the daily visit and after the event (daily) I could then fill in the actual number of hours teaching and so on. The visiting instructor basically doesn't fill out anything. They merely check over it and sign off at the end - if that helps.

Thank you,

Jim

Share this post


Link to post
Share on other sites
James56

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

Edited by James56

Share this post


Link to post
Share on other sites
David Head

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.

Share this post


Link to post
Share on other sites
James56

David,

I made all of the tables and joined them. I didn't change much at all. I did use the table name as the id such as CLUB is clubIdPk and clubIdFk. It's just a bit easier for me to see at a glance I guess. Now I don't know wher to go from here but I'm guessing that formatting a printable form and placing the fields (using portals?) is the next step.

This solution is/was to be that I could create a form like the one I sent. It has to be that way. I need to fill it out on the computer and actually print out 3 copies - one for me, one for the instructor, and one to be sent to our parent org. Using ipads and the cloud would make sense but as with our art it's very old fashion. Using the computer at all took a lot of nagging for years from me. But we've come a long way. I use a PC to run my club completely. Jean Wenmeekers has helped me through a ton of versions over the years and I'm sure he cringes when I email : ) But I'm very grateful to him for all he has done for me and his patience. Especially his patience. Anyway, the form can be created in portrait or landscape, the columns can be expanded as much as paper will allow and I can separate the 'blocks' that contain more than one item. Like Day/Location. By the way the Day refers actually to a Date as explained under the heading of this item. So no Day field is needed but Date fields are. I would like to make the entire thing drop down fields but that probably isn't feasible.

Thanks,

Jim

Share this post


Link to post
Share on other sites
James56

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

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use