Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Help a beginner...database structure questions


christm
 Share

Recommended Posts

Hi

 

Hoping you guys can help - first time using filemaker. I'm using it for my new business which is a dance school. Basically I need to keep track of people registered to the school, what classes they take in what term and what payments they should have made and whether they paid or not.

I don't know if I've done it right but I've set up the following tables

Person Details

Term Details

Class Details

Assignment Details

 

Person Details holds all the personal information and has a unique person id for each record

Term Details holds all the term details

Class Details holds all the class details - including the cost of each class.

Assignment Details tried to link it all together by holding a person id, a term name and a class name.

 

I've managed to create screens that can show the person details and then a portal which shows all the terms and classes that person is assigned to.

So I can see the cost for each individual class the person is doing but I'm now trying to do 2 things.

1. Add a total cost at the bottom of the portal adding up all the classes that person has taken.

2. Find a way to group the portal (or another way of showing the data) by term so that I can sub total cost by term on the screen,

 

Can anyone give me any clues as to whether I'm going about this the right way with my basic database structure and also some help on how to make the additions to the database that I'm trying to do,

 

Thanks

 

Martin

Link to comment
Share on other sites

You're off to a very good start and seem to have a good head for structure!

 

 

I've set up the following tables

Person Details

Term Details

Class Details

Assignment Details

 

Person Details holds all the personal information and has a unique person id for each record

 

So far so good

 

Term Details holds all the term details

Class Details holds all the class details - including the cost of each class.

Assignment Details tried to link it all together by holding a person id, a term name and a class name.

 

Is each class a class of a term? Or does each class reoccur term after term? While not specifically ruling out the structure you went with, I'd say that if each class reoccurs term after term, I'd create a table TermClass that would contain the info about a given class as it exists for a specific term — it would contain the Class ID and the Term ID, it would have its own ID# auto-generated, and it would contain data fields that describe this particular iteration of the class such as Start Date, Status (in progress, pending, completed, cancelled, etc?), and Instructor (which could link to yet another table, a table of instructors, if you wish).

 

Then I'd have a table PersonsInTermClass, which would have a field for TermClass ID linking each record to TermClass (and, through that, to Term and to Class) and a field for Person ID (linking it to Persons).

 

 

 

1. Add a total cost at the bottom of the portal adding up all the classes that person has taken.

 

I'd do this as a subsummary report, which I'd build in the PersonsInTermClass. You could run it for a specific person, or a specific class, or a specific term, and it would be effectively the same report, breaking down the found set of persons x terms x classes as a consequence of sort order. You could have one variation that shows people then, for each person, divided up by terms, and within each term, showing all the classes and a subotal at the bottom for all classes that term, then the next term and its subtotal, and a grand total at the bottom; another variation would show all terms, then, within each term, the classes, within which all the students in the class, subtotals for each class, subtotals for each term which would tally up all the classes for that term, and grand total at the bottom; a third variation could show all classes first, then terms, then students and do the subtotals in that order within those categories.

 

2. Find a way to group the portal (or another way of showing the data) by term so that I can sub total cost by term on the screen

 

 

This is why I'd use the table structure described. The levels of analysis are already inherent in the structure.

Link to comment
Share on other sites

Hi

 

Thanks for the quick reply.

OK - I think I understand where you're coming from.

I'll try it out with a table which includes the class and term details.

In answer to your question - some classes re-occur term after term and some change every term - the start and end times will nearly always change each term.

 

Only question is:

What I really want is a Person Details form with several tabs below the person details (one for contact info, one for class assignments).

Is there a way to do this and have the class assignments tab broken up with sub totals etc?

 

Thanks

 

Martin

Link to comment
Share on other sites

You can't break up a portal with subtotals.

 

You can create a totals field that totals up all the records in the portal, and you can create a portal that dynamically changes which records are visible within the portal.

 

For instance, on a Persons data entry layout with a portal to the classes the person is assigned to (Assignments), you could specify a term and see only the classes for that term in the portal and the totals would be the totals for that term. Or you could even set up a radio button that lets you determine whether the filtering is by term or by instructor name, and a second field in which you specify which term if the radio button is set to term or specify instructor if the radio button is set to instructor name.

 

But if you want a screen that breaks data up by category and provides subtotals for one category value and then goes on to the next category value and has a subtota for that value as well, and so on, you don't do that on your data entry page, nor in any portal, but instead on a subsummary report screen.

 

Well, OK, if you have static values (classes, let's say: if you had a finite number of never-changing classes the names of which were essentially permanent), you could create hardwired calc formulas that sum up all the related values for (let's say) the Physics class, the Music Composition class, the Cultural Anthropology class, and so on. But you'd be constantly diving back into field definitions to modify the calculated structure if the values were not status, and I'm assuming they would not be (and also that there would probably be too many of them for this approach to make sense)

Link to comment
Share on other sites

YES! The first bit you said about filtering the data shown in the portal to only show the current term and thus be able to get the sum of the current term costs - that's exactly what I'm looking to do.

Could you explain a bit more how to do it as I'm not managing it just now.

That way I can have a tab for 'current term' and a tab for 'previous terms'

 

Thanks so much for your help

 

Martin

Link to comment
Share on other sites

Actually on re-reading your post - the bit about radio buttons is even better.

So I could specify a term (or all terms) and the portal would then only show that data?

 

Thanks

 

Martin

Link to comment
Share on other sites

Yep.

 

In this corner, we have the table "Person" and in yonder corner is the table "TermClass".

 

Time to define some new fields.

 

In TermClass, Matchfield, which is a text calc field, =

 

Person ID & "¶" &

Person ID&"|"&Term&"¶" &

Person ID &"|"&InstructorName

 

The result, if you viewed a specific record in browse mode, would look something like:

 

80125

80125|Fall06

80125|Jones, Cynthia

 

Meanwhile, in "Persons", g.MatchValue is a global text field and g.MatchSelect is another global text field; and ParameterMatch is a calc text field, =

 

Person ID & Case(g.MatchSelect≠"Show All", "|"&g.MatchValue)

 

 

g.MatchSelect is formatted with a radio button valuelist containing the values "Show All" and "Constrain". g.MatchValue appears twice on your layout, once wtih a field label of "Specify Instructor" and having a dropdown value list of instructor names and once with a field label of "Specify Term" with a dropdown value list of terms.

 

The portal is owned by a relationship between ParameterMatch in Persons and Matchfield in TermClass, a relationship that will prompt you to name a new Table Occurrence for TermClass, call it "Matching Enrollment" or some such thing.

Link to comment
Share on other sites

Hi

 

Thank you so much for your help. Don't know what I'd have done without it.

I now have a portal that I can filter for whatever term I like.

I'd have never been able to work out how to do it!

 

All I'm missing is how to add a total to the portal to total up the values the portal is showing. Any chance you could advise?

 

Many thanks

 

Martin

Link to comment
Share on other sites

Your portal now shows different data depending on variables you enter in the "control fields" at the top, but the contents of the portal are still defined by a single relationship. So a totals field would be:

 

Sum(PortalRelationship::SomeNumberField)

 

well, actually it could also be SomeTimeField or SomeDateField, but it's more commonly numbers that people want totals for. What is it that you're totalling, cost?

 

Sum(PortalRelationship::Cost)

Link to comment
Share on other sites

Hi

 

OK I'm gutted - I was following it really well up till this last bit and I just can't get it to work.

What I have is

Person Details - with person info

TermClass - with info of terms and classes

Assignment Details - which matches a person to a term

 

My portal is based on Assignment Details but the cost that I want to sum is from TermClass.

Nothing I seem to do is coming out right.

 

Sorry

 

Martin

Link to comment
Share on other sites

In that case, you need a field in Assignment Details which references the cost in TermClass. If the costs stay fixed for the most part, it can be a number field with the Option set to Lookup value from TermClass as an auto-enter option. If, for some reason, the costs change in TermClass and you need the value in Assignment Details to be up-to-the-moment correct, you're better off with a numerical calc field in Assignment Details defined as TermClass::Costs.

 

Whichever way you do it, you end up with a field in Assignment Details which contains the cost, courtesy of having referenced the real cost field in TermClass.

 

And that's the field you reference in your Sum calculation field in the Persons table.

Link to comment
Share on other sites

  • 2 weeks later...

Hi

 

This all worked a treat and I am now up and running with it. Thanks for the help.

It has however thrown in another problem.

Sometimes, a discount needs applied.

I need to be able to apply 2 types of discount.

1. Enable me to enter a discount amount which will be subtracted from the total.

For this - thinking of creating a new column on Assignment Details which can be entered and then changing the Sum Cost calc to be sum(cost-discount)

 

2. An automatic percentage calculation. If someone takes 2 classes they should get a 10% discount, if someone takes 3 or more classes they should get a 20% discount.

Not as sure how to work this into my calculation.

 

Any help would be appreciated

 

Thanks

 

Martin

Link to comment
Share on other sites

Sorry, missed this when you originally posted it.

 

1. Enable me to enter a discount amount which will be subtracted from the total.

 

The discount would be "per the student"? "Per the class"? Or "per the combination of student and class" (the students only gets a discount on that one class, and other students in the same class don't also get a discount)?

 

 

 

2. An automatic percentage calculation. If someone takes 2 classes they should get a 10% discount

 

On both of the two classes, or just on the second of the two? If on both of them, what if they've already been billed for the first class? (Or is that not an issue?)

 

 

, if someone takes 3 or more classes they should get a 20% discount.

Not as sure how to work this into my calculation.

 

Essentially, it's just a matter of which level of analysis to put in your discounts.

 

You have a cost field in TermClass, yes? That's one place a discount could be entered (and would affect everyone taking that class that term). Then you have an Assignment Details table which looks up the value from TermClass. You could have a discount field in that table instead (which would only affect this student taking this one class this term, nothing else). Or, since your Students table has a calc field that does a sum of the cost of all courses being taken, you could have a discount field there (which, if it were just a number field, would affect the cost of all the courses that this student takes; or, if you set it up as a calculation field that kicks in depending on how many related Assignment Details records there are, would affect the cost of all the courses that this student takes but would do so in response to how many courses the student is signed up for).

 

The formula for such a calc field would look something like this, let's call this field DiscountCalc:

 

Case(Count(Assignment Details::Cost)>2, 0.80, Count(Assignment Details::Cost>1, 0.90, 1)

 

Then you multiply the normal totals field, which has a formula like Sum(Assignment Details::Cost), by DiscountCalc. If DiscountCalc is 1, the totals field post discount is the same as the original totals field. If Discount calc is 0.80, they only have to pay 80% (is that the same as a 20% discount? I hate percents and I don't think in them); if 0.90 they only pay 90% (same as 10% discount?).

 

If you want only the second course of two courses to get the two-course discount, that's a bit messier, so I'll wait and see if the above tells you everything you need to know, rather than add a bunch of complexity that you may not need or be interested in.

Link to comment
Share on other sites

Hi

 

Yes, I think that's what I'm looking for. I'll try it out later today.

To try and explain better, the calculation needs to work per student per term.

So if Student A is taking 2 classes in term 1, they should get 10% off each class (which is the same as giving them 10% off the total amount). But then if in Term 2 they only take 1 class they will pay the full amount.

 

Where it gets a bit more complex is if - as you say, someone has already paid full value for a class and then takes another class (or also if someone joins a class halfway through - they would then get a bigger discount). This is where I was talking about having a manual discount value which overrides everything.

What I think I need is also a manual discount field that I can enter in a discount. I then need the total field to look to this manual discount field first. If there is a value in it, then it should ignore any calculations about applying a discount and instead simply subtract this value from the amount. If there isn't a value in it though, it should then look to apply the calculation - if appropriate.

 

Does that make any sense?

 

Thanks

 

Martin

Link to comment
Share on other sites

Hi

 

OK - I couldn't wait, I tried it straight away.

What I've done is this.

Created the following columns on Person_Details

 

Discount Percentage = Case(Count(Assignment Details::Cost)>2; .8; Count(Assignment Details::Cost)>1; .9; 1)

 

Amount Paid = Sum(Assignment Details::Cost)*Discount Percentage

 

Discount Amount Percentage = Case(Count(Assignment Details::Cost)>2; .2; Count(Assignment Details::Cost)>1; .1; 0)

 

Discount Amount = Sum(Assignment Details::Cost)*Discount Amount Percentage

 

This means that on my portal screen now I can have the total cost, then show the discount and then show the cost with the discount applied.

And it seems to be working great. My only concern is that I'm not specifying the term anywhere. It's working fine on the portal as I'm specifying the term within the portal (thanks to the constrain clause you gave me!) but I'm thinking I might hit problems when I try and run any reports off showing what people have paid?

 

Also - on the subjects of reports, I'm trying to create a report that will be grouped by term and then by class and show every pupil within a class. What seems to be happening is that each student is only coming back for 1 class even if they are in 3. Any idea why that would be happening?

 

Thanks again

 

Martin

Link to comment
Share on other sites

To try and explain better, the calculation needs to work per student per term.

 

That means you want the discount field to be here:

 

Then you have an Assignment Details table which looks up the value from TermClass. You could have a discount field in that table instead (which would only affect this student taking this one class this term, nothing else).

 

(you continue...)

 

So if Student A is taking 2 classes in term 1, they should get 10% off each class (which is the same as giving them 10% off the total amount). But then if in Term 2 they only take 1 class they will pay the full amount.

 

If you want it to calculate on-the-fly rather than being manually entered or entered via a script that you run from a button, you'll need a new relationship and at least one additional calc field:

 

• In Assignment Details, you need a text calc that concatenates the unique identifier for student (Student ID) with the identifier for Term (Term ID or whatever), so that it's a single field. I'd do it with a pipe symbol between the two components, e.g, Student ID & "|" & Term ID.

 

• Then you join the Assignment Details table to itself based on that field to itself (a selfjoin relationship), which will prompt you to name the resulting new Table Occurrence, let's call it SameStudent_Term.

 

• Now you know you're also going to need a regular editable number field, to override the calculated discount, and you need to create it before you create the calculated discount (So go ahead and do so now). As you said:

 

Where it gets a bit more complex is if - as you say, someone has already paid full value for a class and then takes another class (or also if someone joins a class halfway through - they would then get a bigger discount). This is where I was talking about having a manual discount value which overrides everything.

 

• Assuming the manual override number field is called DiscountOverride, your final calculated discount field (calc, number) should look like this (give or take changes due to actual fieldnames being different, etc):

 

Case(not isempty(DiscountOverride), DiscountOverride,

Count(SameStudent_Term::Student ID) > 2, 0.80,

Count(SameStudent_Term::Student ID) > 1, 0.90,

1)

 

• Finally, you need a calculated ActualPrice field which is simply Price * Discount.

 

 

• Your totals field in the Students table needs to be edited so that it sums up Assignment Details::ActualPrice instead of the non-discounted course price as originally discussed.

 

 

 

Note that if a student has paid for Class A already and then enrolls in Class B for the same term, which is where we talked about you making use of the manual override for Class B, you also need to manually put in a manual override of 1 (100% — in other words no discount) for Class A, because the calc field will change the value for Class A from 100% to 90% as soon as the Class B entry is made, and that will be wrong if Class A was already paid for at 100%.

Link to comment
Share on other sites

  • 3 weeks later...

Hi

 

Really sorry I've not replied to your post - computer has been playing up. Back online now though :-)

Not had a chance to try out what you suggested - but I will.

 

I have a more pressing concern at the moment though....

Is there a way I can run some kind of report to show all the details of a single term? rather than seeing all the terms? I know I can group by term so all the details for a term will be grouped together, but wondered if there was a way to almost create a report with parameters so I could run for a particular term, class or student etc.

 

Thanks

 

Martin

Link to comment
Share on other sites

You have the "Term" field in TermClass, yes? All you need is a global in Persons in which you specify a Term, and a relationship between that global and the Term field in TermClass, to be able to do a Go to Related Records [show only related, using layout "ReportLayout"].

 

Alternatively, you can go to a useful layout that uses TermClass as its native table, enter Find Mode (no find request specified), Show Custom Dialog ["Specify Term, please" "OK"], Perform Find [], and again you've isolated the specified term within that table.

 

Either way, at that point all you have to do is Sort it as per the sort order of the subsummary report you want to run and you're home free.

Link to comment
Share on other sites

  • 3 weeks later...

Hi

 

Thanks for all help so far. We've now entered term 2 in our classes and I'm looking for more help. Basically we have set up everyone with the classes for term 1. Now 99% of these will stay the same for term 2. Is there a way to duplicate term 1 classes for term 2 for everyone so I can then just remove the one's that are different?

 

Hope that makes sense.

 

Thanks

 

Martin

Link to comment
Share on other sites

In the table that actually has the records for the students x term...

 

... if this were the SECOND (or THIRD or NINETEENTH) time you were doing this, you'd first do a Find for the most recent term's records, to isolate them from other terms' records; in your case, you've only GOT one term's worth so far, yes? So just do a Show All Records in that table.

 

Then create and run this script:

 

Go to Record [First]

Set Variable [$RecNo, 1]

Loop

..Exit Loop If [GetAsNumber($RecNo) > Get(FoundCount)]

..Go to Record [by calculation, $RecNo]

..Duplicate Record/Request

..Omit Record/Request

..Set Variable [$RecNo, GetAsNumber($RecNo) + 1]

End Loop

Link to comment
Share on other sites

  • 3 weeks later...

Hi

 

OK Been workin really hard and have now done everything on the database that was suggested and the reports etc are working great.

The only thing I've not been able to do is the discount thing so that it takes into account the term. i.e if 2 classes in same term then apply discount but if 2 classes over different terms don't apply discount.

 

I've tried the bit suggested about creating the person_id||term_id column and then joining it back onto itself but I still can't get it to work right. Any further ideas to try and get it through to me as I must just not be getting it just now?

 

Thanks again

 

Martin

Link to comment
Share on other sites

  • 2 weeks later...
  • 10 months later...

Whew ! Have tried to follow this thread, but not sure if it's what I need or already have it, and am looking for the next step . I have the following:

 

1.) I have a layout with a drop down Customer ID field and a portal to a related file whch shows the records for a particular Customer ID. A continue button runs a script Go To Related Record and copies those records to a second layout.

 

2.) The second layout has a radio button called Is_Checked with Yes/No for ech record so you can select the records you want. A Continue button runs a script (Perform Find[Restore]) for all records = Y, and gives you a new found set.

 

I've tried putting the radio button in the portal and then running the Perform Find script, but it's not working. Is there a way to do this ?

Link to comment
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.

 Share



×
×
  • Create New...

Important Information

Terms of Use