custom field

[martinwcole]

i have a set
patients
----transactions
--------payments
..........
what i need to do is have multiple payments be linked to a single transaction and multiple transactions linked to a single customer
..........

if i use a common field(patientid) for both tramsactions and payments - all payments for the customer show up in an embedded browse whereas i need only the payments for a single transaction to display - i was thinking about having a "calculated field" in tranactions like patientid + 1 (20001-01) and using that as the linking field between transactions and payments.
.........
but i can't figure out how to create it. patientid is a numeric field

[Stan Mathews]

RE: custom field

You could add a calculated field as described, but you don't need to do so. You could accomplish the same thing with a complex linking expression.

If your patientid is character based and your transaction number is numeric you could use

patientid+" "+str(transaction number)

as the linking expression. I put in the space for easier "readability" if you ever need to view it in the expression builder with the "evaluate" button.

[martinwcole]

RE: custom field

where does the transaction number come from ---

[martinwcole]

RE: custom field

patientid(patients)(20001)
....patientid(transactions)(20001)
....[calculater field(transnumber)=20001-01]
....patientid(transactions)(20001)
....[calculated field(transnumber)=20001-02]
........payments(transnumber) (20001-02)
........payments(transnumber) (20001-02)

[Stan Mathews]

RE: custom field

I didn't look at your structure closely enough.

I would recommend that the payments table be a child of the tranactions table which is a child of the patients table. It seems from your posting, and it's hard to demonstrate without vertical lines like the set builder has, that yur payments table is linked directly to the parents table.

With this structure your problem should vanish.

I assume you have a link patientid-patientid between patients and transactions and a link transaction number-transaction number between transactions and payments.

If you decide you want to make the change you could look at patientid records and filter on the "grandchild" values.

[martinwcole]

RE: custom field

stan - thank's for staying with me
yes
patients ...parent
....transactions ... child (1 to many) - patientid linking
........payments ... grandchild (1 to many)

medicaid may pay only part of a particualer charge/transaction - and an additional payment may be made by the patient for the balance and/or an adjustment may be made as a "payment type" to make up the difference.

the problem is that the customer, a hearing aid company, does not have a unique number for the transaction, like a regular medical office probably would.

i can, and may have to, just add an auto increment field to transactions and use that as the linking field between transactions and payments - but not display it as it would be 'non-sensical.' my thought was that if i could generate my own transaction number which would be the patient id plus the tranasction number for that patient.

patient - 30056
.....transaction 1 30056-01
.....transaction 2 30056-02
..........payment 1 30056-02
..........payment 2 30056-02
..........payment 3 30056-02
.....transaction 3 30056-03
..........payment 1 30056-03

does this make sense

[Willard]

RE: custom field

Martin---> I am n not by any stretch of the imagination an Accountant or do I even uderstand the ramifications of what they do (Jake has a great understanding of such things) In my honest opinion, you are making too much out of this. This is what Quicken does.

Check number is the linking field 'could be 10252, Deposit, ELT etc
cdate(Date) + Check Number is an index
Payee 'Who transaction is made for
(sometimes there are may charges for one payment and is shown on a split screen)
All is one table.

Therefore if you have a date and check number and a payee field you cannot every
get mixed up on records. Everything just needs to be kept within the same sequence. The date field would be Ck_Date and every record would have this date.
Is this what you are looking for? I have been working on duplication Quicken and the simplistic approach is something to marvel at. Very fast. Hope this is what you are looking for.

[Oran Hamilton]

RE: custom field

Martin,

I worked in computer operations in a hospital for a number of years. Each patient had their own medical record number (client id). However, each patient also had a unique account number for each visit. This allowed us to look up a patient by their medical record number and see all of their accounts, or if we had a specific account number, look up the activity for that specific billing. When billing medicare, medicaid, insurance companies, etc. we submitted the account number instead of the medical records number. When the payment came in, the breakdown always had the account number on it so that we could apply the payment to that specific account. Then, adjustments (discounts and so on) were made to that account and if any balance remaining, the patient was billed.

HTH

Oran

[Oran Hamilton]

RE: custom field

Martin,

In reading my post, I realized that I must clarify what a visit meant. It is the initial treatment and all follow up visits. For example, a patient comes to the emergency room and requires stitches and is told to come back for suture removal in 5 days. The charges for the suture removal would be applied to the account number for the initial visit, not a new account number. Hope this makes it a little clearer.

Oran

[martinwcole]

RE: custom field

thank's all ever so much - my main realization is that i was making much abou nothing

my solution in plac and working is this
there are three files:
patient - parent
transaction(charge via cpt code) - child - 1 to many
and
payments - grandchild 1 to many

what i have to do is have more than one payment to a transaction (not the patient per sey)

i put a field in transactions(transnmbr) and made it an autoincrement and added the same field top payments - then made that the linking field for transactions and payments.

works great