Finding grouped records

[amigotto]

Hi Guys,

I have an "expenses" table which includes project, account number, payment date and payment number fields.

In the case where one payment may represent two or more expense items, I can have two or more records with the same payment number, such that when those records are added together, the total payment amount for the same payment number matches the amount debited from the account in the bank statements. For example, if a hotel bill containing room charges and restaurant charges is paid with one cheque or credit card transaction, two records are entered with the same payment number, one for the room and another for the restaurant. Or when reimbursing employee expenses, etc.

I'm required to produce a report which includes only those payments which represent grouped expenses. In other words, I need to find all payments with the same account name, payment date, and payment number, omitting records which have unique payment numbers. I have a layout which shows the data as required but how can I automate the omitting of unique records?

[comment]

For example, if a hotel bill containing room charges and restaurant charges is paid with one cheque or credit card transaction, two records are entered with the same payment number, one for the room and another for the restaurant.

That's not the best method, IMHO. You should have a table of Payments and a related table of PaymentItems. In your example, there would be one Payment with two PaymentItems related to it.

[Fitch]

I agree with Michael, but in answer to your question, it sounds like all you need is the standard FileMaker find duplicates syntax, i.e. do a Find with "!" (no quotes) in the payment number field.

[amigotto]

Since I have many reports set up which use the database the way it is setup now, it would mean reworking the whole database which is not possible at this time. I can't see how I could generate the reports I do now if I had to use separate tables to do so. (I'm not that proficient yet). The way it is set up each payment item in the table is entered as a separate record (which has specific related documentation, accounts, budget items, tax deductions, witholding caclulations,etc).

Tom the find duplicates syntax doesn't work in this case because I'm searching for duplicate entries in one field but at the same time I have to search a specific account (another field).

At the moment I just generate the report and omit the unique records manually. I only takes a minute or so (for a 500 record project) but it would be nice if I could automate it.

Thanks for your help.

[comment]

The way it is set up each payment item in the table is entered as a separate record

How do you generate the payment number - which IIUC needs to be (a) common to all items of the same payment but ( unique among all payments?

Tom the find duplicates syntax doesn't work in this case because I'm searching for duplicate entries in one field but at the same time I have to search a specific account (another field).

Are there entries that have the same payment number but a different account name or payment date?

[amigotto]

How do you generate the payment number - which IIUC needs to be (a) common to all items of the same payment but ( unique among all payments?

Payments are numbered sequentially according to bank statements upon reconciliation (manually). Multiple payment items are usually (but not always) a result of reimbursement and are grouped by a "reimbursement number" field. For example - a credit card bill will have several individual payment items. Each day when payments are made a report is generated. The credit card items are grouped so that the report shows the total amount for the bill, which is what appears on the bank statement. The final financial reporting for the project includes the individual items (with the same payment number) sequentially listed according to the bank statements, and another report which has a subsummary part by payment number which shows all payment totals according to the statements. Another report shows only grouped payments - which is the one I now manually omit unique numbered payments from.

Are there entries that have the same payment number but a different account name or payment date?

Yes, since there are many accounts it is possible to have the same payment number on the same date in different accounts (event the same chque number!), which is why the find duplicates doesn't work.

[amigotto]

Got it! Omit the records whose "reimbursement number" field is empty!

Thanks guys!

Can you explain why having the payment items in a different table is a better model? I should maybe look into this for a future upgrade to the system.

[amigotto]

Woops! Spoke too soon. Although a step closer, reimbursements can have 1 payment item as well, so I still need to omit these. :idot:

[comment]

it is possible to have the same payment number on the same date in different accounts

Then you need to either create a calculation field concatenating the two and look for duplicates there, or define a self-join relationship matching on both and count the related records.

why having the payment items in a different table is a better model?

It is a better model, because any details that describe the overall payment (i.e. apply equally to all of the payment's items) should be entered only once. Entering them twice or more is redundant and error-prone.

Of special interest here is the payment number. I asked how this number is generated, but I cannot see an answer to that.

If it's generated by someone else (e.g. your bank), then in addition to having to enter it more than once, you also have no way to verify it is unique in the payments domain.

OTOH, generating such number within Filemaker is far from trivial.

[amigotto]

Then you need to either create a calculation field concatenating the two and look for duplicates there, or define a self-join relationship matching on both and count the related records.

OK - I'll try this

It is a better model, becuase any details that describe the overall payment (i.e. apply equally to all of the payment's items) should be entered only once. Entering them twice or more is redundant and error-prone.

The fact that it is error prone is what's most important for me - in practice the only fields that describe the overall payment are the date and paymentnumber. But even these are verified at the time of payment or reconciliation, which reduces error to a minimum in this case. So if I create a separate table, that table should include all fields in common between payment items that would need the sam payment number, correct?

Of special interest here is the payment number. I asked how this number is generated, but I cannot see an answer to that.

If it's generated by someone else (e.g. your bank), then in addition to having to enter it more than once, you also have no way to verify it is unique in the payments domain.

OTOH, generating such number within Filemaker is far from trivial.

Bank statements are numbered manually (by hand) on the printed bank statements. These numbers are then entered manually in the database. This is the weakest link in terms of being error prone.

[comment]

So if I create a separate table, that table should include all fields in common between payment items that would need the sam payment number, correct?

Correct. And it should also have a PaymentID field, defined to auto-enter a serial number. Now, if you enter the payment items into a portal on the layout of Payments, they will automatically "inherit" the parent payment's PaymentID. This way nothing depends on your current payment number.