Calculation limited to one per day

[Reid]

I have a mileage field that calculates how much a person is paid for mileage each day. When they put in one of their appointments it automatically fills in this field based on how much they receive which is found in the employee table.

The problem is that at this point it calculates every time they put in an appointment but they only get paid for it once per day. How can I have it check to see if it has already been entered for that date and then leave it blank if one already exists?

Thanks!

[comment]

Here's one way:

OnePerDay.zip

[Reid]

I'm trying to figure out how this works so I can apply it to my database.

Why does the EmpID and Date have to have a relationship when they are part of the same table?

Can you explain what each part of the formula does?

Case ( LogID = SameDateEntries::LogID ; PerDiem )

[comment]

Each record is related to its "siblings" (same employee and same date), including itself. The calculation asks: am I the first record in the siblings group? If yes, return the per diem value.

The first related record is determined by the sort order of the relationship (in this example the default creation order) and is the same for all records in the group. Therefore only one record in each group will get a positive answer to the question.

[Reid]

Your formula is Case ( LogID = SameDateEntries::LogID ; PerDiem )

What if I don't have a LogID field? I probably should have one but is there a way around this?

If not, how can I create a serial number auto field and have it add to all the records I already have?

[Reid]

Also, my "PerDiem" comes from a different table. Do I have to make another relationship then?

Sorry for the trouble...this is just a bit confusing to me.

[comment]

What if I don't have a LogID field?

If you don't have a field that uniquely identifies a record, then this won't work for you. You can solve this by defining a field that auto-enters a serial number. Put the field on the layout, show all records, click in the field and populate it by choosing Records > Replace Field Contents… > Replace with serial numbers (Update...). After that, remove the field from the layout, or make it non-enterable, or not modifiable.

Also, my "PerDiem" comes from a different table.

It doesn't really matter where it comes from. This would work even if the value were related. Hopefully, you are looking it up and storing it in the appointment record - otherwise you'll be in trouble when the amount is updated.