One to one set link

[lcgreene1a]

I have a one to one set link for two tables where the link is:" start date+amount Charged" in the parent and "Start date Amount billed" in the child table All fields are the same type and length in both tables. When the form view is used and date range query run with sort order. 3-4 of the parent records are linked to a child record which does not match the linking fields. However the proper linked record does exist but will not link up. I place and if clause calculated field to test a match on each of the linked fields separately and they return "No" when the form is viewed and the set link matches the wrong record. Has anyone ever encountered where the compound set linking field does this ?

The same erroneous result is presented when the show parent only when child exist is selected and show parent when the child does not exist. Any comment is appreciated.

thx

[Ted Giles]

Re: One to one set link

What is the expression you used?

You can't easily show what you want to using just the two fields to link the Tables.
The Expression will probably be; [ctod(start date)+trim(str(Amount Charged))]
If [Start Date plus Charged] matches [Start Date plus Paid] then a link will have been made. Only matching data will be displayed. Having the first part of the Expression the same name doesn't help matters. You can save it like that but it might be better to use P_Start_Date and B_Start_Date.
If you add a Field called say, ID, then you can more easily identify the data you want.
May I suggest putting the Billed and Paid in the same Table?
It would make it easier for you to find out what is owing/unpaid for a start.

[lcgreene1a]

Re: One to one set link

Ted,

Thanks for the suggestion, however in the expression you have three left hand ( and two at the end of the clause, should there be an equal number to on each side to close the expression within the brackets. Also which string is used for the parent and which is for the child table in the one to one set link ? Also I use different names variations for the dates in the child and parent tables.

[Ted Giles]

Re: One to one set link

The [ and ] are not brackets.
ctod(start date)+trim(str(Amount Charged) )
1 2 1 2 21
This is correct.
The link would be

Parent.
ctod(parent_start_date)+trim(parent_amount_charged)

Child
ctod(child_start_date)+trim(child_amount_paid)

Do you want to post an example of where you are with this? There may be others who can offer suggestions.

[lcgreene]

Re: One to one set link

Ted,

When I entered your script example in the expression widow, it indicated an invalid script. I used your ctod(start date_a)+trim(amount charged) as the parent link. And ctod(state date_b)+trim( amount billed) for the child. Those are the actual names of the two fields in the parent and child tables that are equal and constant in value and format.

I also used show parent only when child exist when I was suing just date and amount as the link, and that is when the train goes off the track. It also links to records in the child of a different year and amount. I also had to change to one to many, and that is not satisfactory since there can be more than one parent on the same date with different amount and vise versa for the child. I need to have a link between the two that is very distinct and works both ways, if there is more than one parent, it should link to the possible children IE

Parent = child 1-1
Parent equal child 1-1 when more than one and increments to the proper record on the same date with differing amounts $

I guess I need a many to many capability that if no multiple exist shows only the one to one if any. Thanks for your help and appreciate ny ideas you may have, I will be doing further checking.

Regards

[Ted Giles]

Re: One to one set link

Can you describe what you are trying to actually do? And why?
The link needs to be a calculated field with the same name in both tables. The contents names will be different.
I'll look again tomorrow.

[Ted Giles]

Re: One to one set link

Try this.
See attachment.
There will only be corresponding dtata if it is exactly the same in Billed and Received.

[lcgreene]

Re: One to one set link

Ted,

Thanks for the test example. However when I edited the link on the set, there is no entry in the expression format. The only thing observable is "link" in the linked box for the parent and child. Let me explain what I am doing. First my current link format expression of start_ date_ + amoun_ billed to Start_ dat_ B + billed_amount, parent to child yields a 90% accurate match IE 10% shows the wrong record for the child linked to a parent.

I have two tables, one is for the primary insurance claim record and the second is for the secondary insurance claim record. When a person goes to the Doctor, hospital etc. there is a primary claim created by the provider which is sent to the carrier. When the carrier processes the claim a record is sent to the secondary insurer. This is called coordination of benefits. The client gets two EOB's "explanation of Benefits" as a product of the process.

In most cases the EOB's are generally a single instance of service with one to two charges by the same entity yielding a total charge for that specific visit. On some occasions, generally when visiting the out patient service at a hospital, there is a single date charge comprised of several entries for different services and Doctors.

Here is an example :
Parent Child
Primary Secondary

7/1/2011 Dr Brown 35.00 7/1/2011 Dr. Brown 35.00 (one to one)

7/1/2011 Dr. Jackson 40.00 7/1/2011 Dr. Jackson 40.00 (one to one)


7/2/2011 Dr. Lime 50.00 7/2/2011 Dr. Lime 50.00 (one to one)

7/3/2011 Dr. Jay 25.00 7/3/2011 Baptist Hosp 100.00 (Many to one)
7/3/2011 Dr. Brown 25.00
7/3/2011 Dr. Red 75.00

[lcgreene]

Re: One to one set link

Ted,

I think I have found the solution. the expression is: cdate(start date a)+ amount charge for parent
cdate(start date b)+ amount billed. for child

Ctod take a character string and converts it to date, the date format in the table is "date" so ctod generate an error. Trim works on character strings, consequently it cannot be used for "amount" which is either a numeric or numeric in currency format, it too generates an error.

Regards,

Lou Greene
Navarre, Florida

[Ted Giles]

Re: One to one set link

Sincere Apologies Lou, you are indeed correct.
It should be DTOC. That is what is in the example I sent. (Which works. Suggest you disect it.
Sorry to have wasted so much of your time over a misspelling!

[lcgreene]

Re: One to one set link

Ted,

It was not a waste of time. It is some what like there are never any dumb questions. It was a good learning experience. Thanks for the time.

Regards,

Lou

[lcgreene1a]

Re: One to one set link

Thanks for the willingness to help. I solved the problem while back, just getting back tot he forum. Have a good day.