Relating Reservations and Deposits and Payments

[Brisprad]

Hi again,

I'm having more relationship issues-the FileMaker kind, not in my life.

My project is a kennel management system. I have most of my tables created and related the way I think they should be. However, I'm very confused when it comes to setting up for payments and deposits. I specifically can't see for sure how to do the deposits. Like a people hotel, a deposit is taken for peak times. It is then applied to the invoice at check out. I have charges related to Payments via a table called Payments_applied since multiple types of payments might be made (i.e. maybe some cash and some credit card). I have the deposits related to the Reservation. I have a field on the charges table that's a calc to show all deposits taken for the Reservation. The payment then would be the difference.

Does this sound right? It does to me but I'm not a pro yet! I've attached my relationship graph to help. Thanks for any help!

Relationships.pdf

[ThatOneGuy]

Hi Brisprad:

I think you're generally on the right path here. Your Table Occurrence Graph reflects a good understanding in how you linked (1) Charges to Reservations and (2) Deposits to Reservations.

Would it not then follow that Payments should/could be linked directly to Reservations in a similar manner? I believe the answer is "yes," but I think it also generates other questions.

Deposits vs Payments

You might consider consolidating these two "things" into one table. Arguably, a deposit represents a payment that is not applied until the transaction is settled. In that sense, it is a temporary status.... If this could be achieved, you'll have a more-straightforward model to work with for both (1) relationships and (2) bookkeeping calculations.

This streamlining would then allow you to compare the sum of Charges against the sum of Payments. A "balance due" calculation would subtract Deposits/Payments from Charges, and those "amounts" can be obtained through their related sums.

And you would still be able to isolate Deposits under the Reservation. The Payments table would need a "status" field where you can indicate the record as a "deposit." In the Reservations table, you'll need a field to serve as a constant to filter, or isolate, those "deposit" Payments. (I posted an example file for filtering relationships a few days ago in another topic ... which can be found here ... and I'm confident you'll understand how to apply those techniques in your project.)

Finally, I believe you would be able to eliminate the "payments applied" aspect of your model. I'm bettin' that little part has been causing some headaches. It was there in your graph that you may have wandered off into the woods. There are usually many ways to approach a problem in FM, so other developers may differ. Nonetheless, your structure currently relates Payments to Charges ... where it seems you would want to relate Payments to the Reservation, itself.

So I suppose the bad news is that you have some restructuring to do. The good news is you'll end up with a simpler model to maintain.

Let us know how it goes and, of course, whether you have any questions.

[Brisprad]

Hi ThatOneGuy,

Thanks for the suggestions! I really appreciate it. I was trying to figure out a way that a Payment could be distinguished between a deposit and a payment for the charges. The scary thing is after seeing your example file I actually understand! I did look things over last night and decided that the Payments Applied wasn't nescessary (I had seen a post here that was similar and it seemed it would apply for my situation).

The only thing I can't seem to grasp then how should Charges relate to the Reservation? I can see that with your suggestion, from a Reservation layout, I could see the payments applied towards it (which would be good for the future when we look back on history). I'm trying to improve our current software and some of this is modeled a little on it. Our current software creates a Charges Detail when a pet leaves-it shows the payment info and the total charges. But the record itself is a separate record from the Reservation. Even though there's 1000 things I dislike about the software, this I thought made sense.

I'm currently working on a prototype to get approval for a more robust version. I get frustrated when things don't work exactly right but for some reason, I still really enjoy doing this!

Thanks again for your help!

Brisprad

[ThatOneGuy]

Thanks for the suggestions! I really appreciate it.

The pleasure's mine! I just hope my remarks were accurate and haven't made your task more difficult.

The only thing I can't seem to grasp then how should Charges relate to the Reservation?

Just as it shows on your TOG pdf, it seems the relationship of Reservations::zkp_reservation_id = Charges::zkf_reservations_id provides a very suitable means. If you allow "Record Creation" through the relationship portal, you've also simplified that process, however ...

Our current software creates a Charges Detail when a pet leaves-it shows the payment info and the total charges. But the record itself is a separate record from the Reservation.

Despite what one might call it (A rose is a rose by any other name.... Shakespeare, right?), this sounds a lot like an Invoices model. I tried to think if a given Reservation for a pet could require more than one Invoice ... I figured it might be possible but perhaps not likely, so I didn't go into the model in my previous post (that "first, do no harm" thing), but you may have one on your hands.

If so, think in terms of ...

• Reservations having Invoices as children

• Invoices having Charges as children, and

• Invoices having Payments as children

Your Graph demonstrates a competent use of primary and foreign keys to drive your relationships. With a continued implementation of those concepts, Charges and Payments can still be related back to Reservations with additonal table occurrences.

A word of caution ... The model described above will work well if you're confident a relatively few number of Charges and Payments will accrue under a given Reservation's Invoice. There's no magic number, but it's about having enough rows displayed in a portal so that printing does not become a problem. It may be 5 or 10 items, depending on how your "print" layout is designed. (Must it all fit on one page, for example?) On the other hand, if you're facing "a lot" of Charges and Payments from within one Reservation/Invoice, we'll have to entertain a Line Items model so Reporting and Printing can be simplified. If you want to mull this over, contemplate how you could integrate both Charges and Payments into a single Items table....

Yet, here's where I have to double-back ... If you indeed have a relatively few number of Charges and Payments, the "Charges Detail" invoice model may actually be superfluous. Seems one could simply create a print layout for the Reservation with those finite portals relating directly to Charges and Payments.... I guess a crucial question is whether the folks from whom you must have approval would agree to this change in process.

Again, I hope this gets you further down the line. Let us know.

I get frustrated when things don't work exactly right but for some reason, I still really enjoy doing this!

Software design is seldom a rational or linear process, but inspiration is key! Keep up the good work!

[Brisprad]

Thanks again ThatOneGuy!

After I posted about relating the Charges and Payments, I realized it was fairly easy to do.

I got a fair amount done yesterday on my layouts. I had some done but found better ways of implementing a few ideas. I also realized that I could use your suggestion about filtering portals for the pet table. I have a portal on the client page that lists their pets. Unfortunately, pets pass away and once they're gone, we don't want them to show in the active list. (I'll eventually add a script to make a new reservation for all pets (or some) for a client). I'm going to make add a status type field so I can have a portal each of active and deceased. It's a standard for animal hospitals as well-you don't want to remind a client about a pet that recently passed away-if it isn't on their active list you know why.

Thanks again for the great advice. I do really appreciate it.

Brisprad

[ThatOneGuy]

Glad you found our dialog helpful. Keep up the good work! :waytogo: