Combining two tables

Agnethef · 2011-11-21

I have two tables looking like this:

Table A: Medication Log
Field 1: Date (Date)
Field 2: Patient number (Integer)
Field 3: A (Integer)
Field 4: B (Integer)
Filed 5: C (Integer)

Table B: Treatments
Field 1: Id number (Integer)
Field 2: A (Integer)
Field 3: B (Integer)
Field 4: C (Integer)
Field 5: Resting days (Integer)

In my Medication table I have several records for each patient on each date. The combination of A, B and C gives the code for the treatment as defined in Table B.

Now my problem is that I would like to make a report that for each patient on a specific treatment date (given by me) tells me the treatment he/she has received with the largest number of Resting Days.

Please advice.
Thanks.

matttrax · 2011-11-21

Well, think about what you want to do without the code and how you would do that manually.

You would start with data from your Log table and find matching data in the Treatment table. You would set a filter to find that matching data. (SETRANGE, SETFILTER)
Since you will likely have more than one entry that matches you want that data sorted a certain way so that the largest rest is first (SETCURRENTKEY)

If you don't know what those things mean then you need to read up with the NAV development material.

Marije_Brummel · 2011-11-21

Please, please, please learn NAV before you proceed.

Developing NAV, the NAV way is totally different from whatever you did before.

I am doing a level 100 introduction session at Decisions in two weeks. Here I explain the basic differences between traditional design and NAV design and why it is so important to develop the NAV way

http://decisions.msdynamicsworld.com/se ... ion-design

Agnethef · 2011-11-22

I Will register for the introduction session and hopefully learn a lot!

Thanks.

Andwian · 2011-11-22

Agnethef wrote:

Now my problem is that I would like to make a report that for each patient on a specific treatment date (given by me) tells me the treatment he/she has received with the largest number of Resting Days.

Would you explain it in more detail with the example of the records, and the expected results?

Agnethef · 2011-11-23

I have tried the following:

IF Restingdays > 0D THEN BEGIN
Treatments.RESET;
Treatments.SETCURRENTKEY(Restingdays,A,B,C);
Treatments.SETFILTER(Restingdays,'>0');
IF Treatments.FIND('-') THEN REPEAT
MedicationLog.RESET;
MedicationLog.SETRANGE(Treatments.Nummer, Nummer);
MedicationLog.SETRANGE(Medicationdate,Date entered by user);
MedicationLog.SETRANGE(A,Treatments.A);
MedicationLog.SETRANGE(B,Treatments.B);
MedicationLog.SETRANGE(C,Treatments.C);
IF MedicationLog.FINDLAST THEN
Treatment := Treatments +
FORMAT(Treatments.A) +
FORMAT(Treatments.B) +
FORMAT(Treatments.C) +;
UNTIL Treatments.NEXT = 0;
END;

It does give me the the treatments given on the specified date entered by the user, but it gives me all of the treatments given on that date. I only need the treament with the most restingdays.

I know it must be something with the sorting but I don't know how to fix it.

Thanks.

Andwian · 2011-11-23

Agnethef wrote:

It does give me the the treatments given on the specified date entered by the user, but it gives me all of the treatments given on that date. I only need the treament with the most restingdays.

I mean: could you give me some records, scenario it, and then what is the expected result.

Then, we could run into your solution further. Since I could not understand well what is your need.

Agnethef · 2011-11-23

Here is a Scenario - please see attached image:

Andwian · 2011-11-23

What is the link (foreign key) between those both tables? :-k

Agnethef · 2011-11-24

The link is the combination of A, B and C

Each combination is a treatment.

Thank you for your patience.

Andwian · 2011-11-24

Are you sure? I could not find none the combination of ABC in Medication that matches in the Treatment.

Andwian · 2011-11-24

Let see if I could understand your need well:
I assume that this is not using report to generate.

Medication.RESET;
Medication.SETRANGE(Date,UserInput);
Medication.SETCURRENTKEY("Patient No."); //Sort ascending by Patient No.
IF Medication.FINDSET THEN BEGIN
	REPEAT
		Medication2 := Medication;
		Medication2.SETRANGE("Patient No.", Medication."Patient No."); //Medication2 is Medication per Patient ID
		IF Medication2.FINDSET THEN BEGIN
			MaxRestingDays := 0;
			REPEAT
				Treatment.RESET;
				Treatment.SETRANGE(A, Medication2.A); //I guess A, B, and C is the table key, hence you can use GET statement to simplify
				Treatment.SETRANGE(B, Medication2.B);
				Treatment.SETRANGE(C, Medication2.C);
				IF Treatment.FINDSET AND MaxRestingDays < Treatment."Resting Days" THEN BEGIN //Search for the Max one
					MaxTreatmentID := Treatment."Treatment ID";
					MaxRestingDays := Treatment."Resting Days";
				END;
			UNTIL Medication2.Next = 0;
		MESSAGE('Patient No.=', Medication."Patient No.", 'Max Resting Days =', MaxRestingDays, 'Treatment ID =', MaxTreatmentID);
		Medication.FINDLAST;
		END;
	UNTIL Medication.NEXT = 0;  //Advance to the next Patient ID
END;

Unfortunately I don't have NAV on-hand. Hence, let see if the imaginary-compiler is still working well

Agnethef · 2011-11-29

YES! Your imaginary compiler is still working very well!

Thank you very much.

Combining two tables

Comments

Categories