Combo lookup

[JAF]

If I have the following table definitions:

Table	    Fields
Patient	    PtId
Policy	    PolicyId, PtId, CarrierId
Carrier	    CarrierId

Patient to Policy is 1:M.
Carrier to Policy is 1:M.

I need a dialog that lists the patient and allows me to select the pertinent policy from the list of policies for just this patient. When I select a policy I want the carrier field to automatically update as well. I cannot for the life of me figure out how to do this.

I have tried creating a set as:

Patient
    1:M Policy (PtId->PtId)
          1:1 Carrier (CarrierId->CarrierId)

Do I need a set? How can I create this sort of combo box lookup?

[robert]

Re: Combo lookup

Why not put policyid and carrierid fields in the Patient table. You could use the field rules to make the policyid a lookup field to the policy table and specify filling in both the policyid and the carrierid fields. As far as I can see, you don't need the carrier table at all since the carrierID is already in the policy table.

[Bonbon]

Re: Combo lookup

Hi Jeff,

Not quite sure what you're trying to achieve, but I've had a go ... as requested, the dropdown shows a list of policies for just this patient (but so does the embedded browse, so maybe I've misunderstood something here).

Using your set definition, I've used a Record-List Edit Combo Box, whose field is policy->POLICYID. Under the "Choices" tab of Properties, the return value is PolicyId from the Policy table, and the filter is PtId (from Policy) = patient->Ptid.

I've then added PolicyId and CarrierId as columns.

Does this set you off down the right track at all ?

[Tom Cone Jr]

Re: Combo lookup

Jeff, I wonder if creating a "Mapped Table" to serve as lookup source would be an easy solution? From the helps...

Here are some ways in which a mapped table can be used:

You want to include data from multiple tables in a Table Lookup Field Rule. The Field Rule editor only allows you to select Tables (not Sets) as the data source for a Lookup Field Rule. To solve this problem, you define a mapped table that joins all of the tables that you would like to use in the Lookup Field Rule, and then when you define the Field Rule, you specify the mapped table as the lookup data source.

[robert]

Re: Combo lookup

This might be a work around for the "conditional table lookup" that doesn't work (at least in V8 and V9).

As I understand the original post, there is no table with policy IDs for just one patient. He wants to look at all the policy IDs and select the one to use for this patient.

[audrummer15]

Re: Combo lookup

Bonbon, what filter are you using in your Record-List Edit Combo Box?

[Bonbon]

Re: Combo lookup

The filter is on patient ID (PtId) from the Policy table, matching PtID from the Patient table (which is what my main form is based on). So when you are looking at any one patient record, the combo only shows policies for that patient.

Once the user has clicked a row in the drop-down, to get to the selected CarrierId, you could use a lookup expression, along the lines of

lookup("Policy","PolicyId = '" + Cmbpolicyid.text + "'","CarrierId")

(where Cmbpolicyid is the name of the combo box), and put the result into a variable or whatever. Use the combo's OnChange event to do this. It works because the combo returns PolicyId, which (hopefully) is unique.

I'm sure there must be better ways to do this, but I've tried it, and at least it does work. I've got it popping up a message to say what the selected CarrierId is.

Looking back ... maybe we DO want a full list of policies, not just those for a selected patient, so maybe this isn't the right approach...

.. in which case, maybe you could just remove the filter ...

[audrummer15]

Re: Combo lookup

No thats exactly what is trying to be accomplished, but when I apply the filter, PtId = PtId, it shows NO policyId's at all. That is what is so confusing.

[Bonbon]

Re: Combo lookup

Here's a jpg of the filter setting - does this help ?

[robert]

Re: Combo lookup

I wonder what happened to JAF (the person who started this whole thing)?

[JAF]

Re: Combo lookup

I've been unable to respond due to other conflicts. Thanks for the suggestions. I'm a little bit stumped (being new and otherwise highly distracted). I've attached a small database with minimal data in it. It has a few patients, carriers, and policies. Each table is required as a patient can have multiple policies with the same or different carrier. What I want to do is create a dialog listing a patient and allowing me to select which policy (and associated carrier) to use for the next step.

It looks as though Bonbon has solved this yet I cannot reproduce the same result.

Any suggestions or working examples are greatly appreciated. Thanks.

[Bonbon]

Re: Combo lookup

I didn't see your attachment, so here is the tiny test DB I was working on - I don't know if it is anything like what you are after.

If this is no good, Tom Cone's suggestions way back about setting up a Mapped Table, and then using Field Rules, looks excellent - it's probably the way I would go, having had time to think about it (and Tom knows his stuff in a big way).

If you want to have another go at attaching your DB, I (and others, I'm sure) will be glad to have a look at it when time permits. Don't forget to hit the "upload" button when setting up your attachment (sorry if I'm teaching grandma to suck eggs).

[JAF]

Re: Combo lookup

Hopefully the attachment "sticks" this time. I"m not sure why it didn't go last time.

[Bonbon]

Re: Combo lookup

I haven't had a chance to get stuck into this yet (I'll have a proper look over the weekend), but to start you off, try using a Record-List Edit Combo Box (drag one in from the Toolbox), instead of an ordinary Combo Box, and have a look at the "Choices" tab under its properties. There's a lot more you can do with this than with a plain Combo.

[Bonbon]

Re: Combo lookup

Hi,

I think part of the problem is that each patient can have more than one policy. So, on your form, if you try to associate just one of those policies with a record at the patient level (e.g. via a drop-down combo), you will get problems.

For example, Sam Adams has 2 polices: 200 / ABCD, and 201 / efgh. If you set up a combo box to show PatientPolicy, and choose "efgh", you will probably find that the record for policy 200 now shows a PatientPolicy of efgh, instead of ABCD, and your PatientPolicy data is now wrong.

I think the way to go is to use an embedded browse, which shows all of the policies which relate to a single patient. You can set up a form to do this really quickly, in the Forms tab, by creating a new form based on the Policies set, and clicking "Next" to accept all of the defaults. This now shows you all of the polices for any one patient.

From this point on, I'm not sure what is required. I thought maybe PatientPolicy (in the embedded browse) could be set up as a drop-down list, using Field Rules, but the Policy table is not set up for this - each row of the Policy table relates to an individual client. I have a feeling that a bit of restructuring of the data might be required (e.g. should there perhaps be a Policy table which just shows polices, as opposed to retlating them to patients ?), but without more detailed knowledge of the business, I can't say for sure.