topic Re: Help with Circular Reference in App Development

Help with Circular Reference

Cory — Tue, 20 Aug 2019 17:52:56 GMT

I've got a problem with a circular reference that seem like it should be easy to overcome, but I just can't seem to figure it out. We have drugs that get dispensed to a program on a date, we also have surgeries that are done for a program on a date. We need to be able to determine the quantity of a specific drug that was used on a date or in a date range and the program that used it. We also need to be able to count the number of surgeries on a date or in a date range for a specific program. Ultimately we need to see the average usage of a drug per surgery. I'm having a hard time resolving the issue below:

Any help is appreciated.

Cory

Re: Help with Circular Reference

treysmithdev — Wed, 21 Aug 2019 00:20:39 GMT

You need to move the Program Description field to your link(bridge) table. Then have your DrugUsage and Surgery tables link to the link table using concatenated key:

Eg.

DrugUsageKey&'|'&ProgramDescription as %DrugUsageProgramKey,

Re: Help with Circular Reference

Cory — Wed, 21 Aug 2019 12:50:44 GMT

So add it as a distinct field and as part of the key in the date bridge?

Cory

Re: Help with Circular Reference

treysmithdev — Wed, 21 Aug 2019 13:00:28 GMT

Yeah. Something like this:

DrugUsage: Load DrugUsageKey &'|'& ProgramDescription as %DrugUsageProgramKey, DrugUsageKey, ProgramDescription d_ProgramDescription, DispenseDate, .... From ... Surgery: Load SurgeryID &'|'& ProgramDescription as %SurgeryProgramKey, SurgeryID, ProgramDescription as s_ProgramDescription, ORDate, .... From .... DateBridge: Load distinct %DrugUsageProgramKey, d_ProgramDescription as ProgramDescription, DispenseDate as Date, 'Drug' as DateType Resident DrugUsage; Concatenate(DateBridge): Load distinct %SurgeryProgramKey, s_ProgramDescription as ProgramDescription, ORDate as Date, 'Surgery' as DateType Resident Surgery; Drop Fields d_ProgramDescription, s_ProgramDescription;