Don't see why you need GetField for this, you can just refer directly to the the fields by name here, but the final line would be:
List ( "Cash" ; "Check" )
Try this for you calc:
Cash > 0 and Cheque > 0 ; "Cash¶Cheque" ;
Cash > 0 ; "Cash" ;
Cheque > 0 ; "Cheque" ;
Invoice Total ≤ 0 ; "Discount" ;
Total Paid ≥ 0 and Owed < 0 ; "Acct Balance" )
The line in blue needs to go before the lines in red because Case tests are evaluated sequentially.
Alright. That worked like a charm, but now I'm wondering if it's going to do what I had hoped. I'd like to run a Daily Receipt report with a subsummary part that is sorted by this payment method. Unfortunately, now that more than one value is possible in the Payment Method field (switched from a radio to checkbox set), it's not sorting at all.
I can think of a way to do this with a Grand Summary part instead, but that prevents me from displaying the actual records and amounts below each different method of payment.
Hmmm, If someone is making a cheque and cash payment, that's two payments no? If you enter the two as separate entries, then your report will work and you don't need the above calculation in your check box field.
Sometimes it works out better to have a related table of sub payments and each payment ledger record computes the sum of these sub payments. That enables you to have a portal listing each sub payment (one for check, one for cash, one for... etc.). If you use that approach, you can create your summary report on a layout based in this sub-payment table and you'll get your break downs by payment type.
That's an intriguing idea, but I get the feeling that it will overly complicate the process. With all of the tables that I'm weaving together, I hesitate adding another table that doubles or triples the amount of records I have to deal with to make the computations work. My concerns are primarily cosmetic and ease of legibility, I suppose. I'm sure I can make the Grand Summary work if I need it to. It just isn't as easy to read.
Keep in mind I made two suggestions. Log two payments or add a sub payments table. Either way does what you appear to need here: a way to log the amount paid by cheque and the amount paid in cash. The "best fit" will depend on your business practices.
I'm also suggesting a single amount field for all payment amounts. Put the number in one field and use a payment method field to record what type of payement is being made. The result may be more records-- if you use a subpayment table--but much fewer fields in a simpler, easier to use structure.
Thanks for the responses. I had already been logging two separate payments if the method was multiple, and I just wanted to streamline the process to reduce the amount of time between customers. I didn't mean to just brush the suggestion off. My concern with the sub payment table is that there will definitely need to be AT LEAST one record there for every receipt record, sometimes more (though admittedly not that often). So I'm essentially creating two records for every transaction instead of one, which could get dicey over time and seems like an unnecessary compromise on file size. I will, however, keep your suggestion in mind, as it may prove to be a more elegant solution to something else I am working one. Thanks again.
You know, from the outset I had wondered about the usefulness of having the calculation at all, but in the end, not knowing how your database was structured, I decided to simply answer the question at hand and offer the calculation. But in the ensuing discussion, it's become obvious that this is not the solution. Phil's suggestion of having a related table is correct.
Ok, so you have receipts and some of those receipts have more than one payment method. That's a "one-to-many" relationship and to correctly structure that into your database, you need two related tables (Receipts and Payments) related by a unique Receipt_ID number. The Payments table should be set up as a portal on your Receipts layout and with the Payments table set up to "Allow creation of records in this table via this relationship" (in the Edit Relationship dialog box). The calculation given above should be removed and the checkbox field should be changed back to a radio button field. The radio button field should be created in the Payments table, not the Receipts table. The radio button field is then added to the portal so that each Payment record in the portal can be individually categorised as "Cash", "Check", "Credit", etc. There should only be ONE field (in the Payments table) for entering the dollar amount of each Payment, not separate "Cash", "Cheque" and "Credit" fields. Set up in this way, the portal will allow you to enter multiple Payment records related to a single Receipt record. The report will be based on the Payments table and should be set up to summarise by the radio button field.
> I get the feeling that it will overly complicate the process
Having a separate table for Payments is not "overly complicating" things, it's standard practice for a situation like yours, even if having "multiple Payments for one Receipt" is something that only happens infrequently.
> So I'm essentially creating two records for every transaction instead of one, which could get dicey over time and seems like an unnecessary compromise on file size.
Yes, every transaction would have one Receipt record and at least one related Payment record, but there's nothing "dicey" about that whatsoever. And your fears about file size are groundless. Having a separate Payments table will not blow out your file to an unreasonable size.
> I had already been logging two separate payments if the method was multiple
This is not good database design because creating two Receipt records to account for multiple payment methods will result in at least some of your data being unnecessarily duplicated, which is not a "streamlined" approach. Having a Receipts table and a related Payments table is the streamlined approach.
Thanks for the explanation, Nick. As you can tell, I'm not exactly trained in good database design. I "inherited" the database that my company now uses (created in FMP 3 and never updated), and the method that we have in place is how things were originally designed. I have been updating (and making some significant improvements to) the database based on how things were done in the past, and so a related Payments table was completely off the radar. If I put one of those in place, how will a record import proceed from the old version (one payment and one payment type) to the new (two different tables)? Am I importing the same records twice based on the invoice id#?
> As you can tell, I'm not exactly trained in good database design.
Neither was I when I first stared using Filemaker!
> I "inherited" the database that my company now uses (created in FMP 3 and never updated), and the method that we have in place is how things were originally designed.
Ah, I see, a difficult situation, but not impossible to overcome.
> I have been updating (and making some significant improvements to) the database based on how things were done in the past
Good to hear!
> If I put one of those in place, how will a record import proceed from the old version (one payment and one payment type) to the new (two different tables)? Am I importing the same records twice based on the invoice id#?
Ok, so every Receipt record has an Invoice ID#? In that case, it shouldn't be too difficult.
1. Make a backup of your database and test your backup to make sure that it's functioning normally. In fact, make two backups, just in case.
2. Then, from within your original Receipts database, go to File menu > Import Records > File. Select your Receipts database.
3. A dialog box called "Import Field Mapping" will pop up. Select the Receipts table under the "Source" menu. In the Target menu, select "New Table". It will probably say "Receipts 2". Select this, you can change the name of the new table later.
4. By clicking the little arrows between the fields, you can select which fields to map across to the new table. Make sure that you map the Invoice_ID, Amount and Payment_type fields into the new table.
5. Select "Add new records" under the "Import Action" section, then click the Import button. A new table called "Receipts 2" will be created. It will contain new records with the mapped fields.
6. Open Manage Database and change the name of the new table to Payments. Go the Relationships tab and rename the "Receipts 2" table occurence as well.
7. Use Invoice_ID as the key field to relate the Receipts table to the Payments table.
8. Double-click the linking icon between the two table occurences in the Relationships graph and select "Allow creation..." and "Delete related records..." under the Payments table. Then click OK to exit the Manage Database dialog.
9. Set up the portal in your Receipts layout, as I described above. You should then see the related Payment records in the portal.
10. If everything is working the way it's supposed to at this point, then you can safely delete the redundant Amount field and the Payment_type field from the Receipts table, because this data now exists in the Payments table.
11. Then delete all the redundant records in the Receipts table, that is, the "extra" Receipt records that were originally created to account for multiple payments. Of course, don't delete the initial record of each group of multiple Receipt records, only delete the "extra" records.
12. If things went seriously belly up somewhere along the line, you can always delete the file, create a copy from one of your backups, and start over.
Wow! Thanks, Nick. I didn't realize I could create a new table directly from the file import. Yes, fortunately there is an invoice id# for all of the existing records. (Even if there weren't, that wouldn't be too difficult to overcome). This looks like the solution I was looking for.
Okay, one other question that is a bit unrelated...
I'm trying to run a script that locks a record to prevent editing after printing it. The script simply sets a lock field value to 1. That is what I've read is the best way to do it. Then that field can serve as an access privilege grantor or something of that sort.
Unfortunately, I cannot figure out how to set access privileges based on that field. Am I missing something?