Summaries from two files

[Arttu Lahdenpera]

Hi !

I have a problem with creating a report where there is in the first file persons (Person1, person2 with different activities (act1,act2 etc.)

In a second file there are same persons and different activities (act3, act4)

Now I would need a report with amount of activity per person from these two files:

Person1........act1.........Count_of_act1

...............act2.........Count_of _act2

Person2........act1.........Count_of_act1

...............act2.........Count_of_act2

etc

How to do this ??

Thanks in advance

Arttu

[Ugo DI LUCA]

Assuming you have 3 files :

Persons.fp5

ActivitiesGroup1.fp5

ActivitiesGroup2.fp5

And that each activity in the 2 ActivitiesGroup files are separate records holding either the Person_Id and the Activity_ # (just change it to Activity Name if you wish)

1. Create in the Person.fp5 as many globals (number result) as the number of activities type :

g_Act1, g_Act2, g_Act3

and an additional global = g_ChosenActivity.

....

2. Create in the Person.fp5 as many calculations as globals.

c_MatchAct1perPerson_ID = Person_ID&"-"&g_Act1

c_MatchAct2perPerson_ID = Person_ID&"-"&g_Act2

c_MatchAct3perPerson_ID = Person_ID&"-"&g_Act2

...

c_MatchChosenActperPerson_ID = Person_ID &"-"& g_ChosenActivity

3. In each ActivitiesGroup, create a calculation (stored)

c_ActivityPerPerson_ID = Person_ID&"-"& Activity#

4. Back in the Person.fp5,

Create as many relationships as the number of calculations. Select the File where this kind of activity is stored.

RelMatchActivity1PerPerson c_MatchAct1perPerson_ID::ActivityGroup(1or2):c_ActivityPerPerson_ID

RelMatchActivity2PerPerson c_MatchAct2perPerson_ID::ActivityGroup(1or2):c_ActivityPerPerson_ID

...

5. Add 2 other relationships :

RelMatchAnyctivityPerPersoninGroup1

c_MatchChosenActperPerson_ID::ActivityGroup1:c_ActivityPerPerson_ID

RelMatchAnyctivityPerPersoninGroup2

c_MatchChosenActperPerson_ID::ActivityGroup2:c_ActivityPerPerson_ID

6. Create as many calculations as relationships:

c_CountAct1PerPersonID = Count(::RelMatchActivity1PerPerson::Person_ID)

c_CountAct2PerPersonID = Count(::RelMatchActivity2PerPerson::Person_ID)

....

7. The global, calcs and relationships shown in italics could be useful to display separate counts of activities, using a value list of activities to populate the global "g_ChosenActivity".

You would need to create 2 value lists for each group (or have a custom VL) listing the Activity# values from each file, and attach this Value list to the global field.

To have it plain working, you could add these fields :

c_AllValuesFromFile1 = ValueListItems(Status(CurrentFileName), "yourValueList1")

c_AllValuesFromFile2 = ValueListItems(Status(CurrentFileName), "yourValueList2")

Ultimately, a set of little script (and 2buttons) to set the global so that it matches the whole set of activities for each files, therefore leading with a Count(all activities per Person_ID).

i.e. for file 1 Activities.

Set Fied (g_ChosenActivity, c_AllValuesFromFile1)

Now, it would be cool to know if you really need to separate the Activities in 2 files

[Arttu Lahdenpera]

Uh oh... =) It may take some time for me to digest and understand all that :

Okay, the reason, why I have two different files is that In the first file there are dispathed missions for the emergency unit and in the second file consultations by phone. The nature of these activities are so different that I decided to establish two files. Maybe stupid ?

But Thank You so much for help !

Arttu

[Ugo DI LUCA]

Hi,

The nature of these activities are so different that I decided to establish two files

Nature # Type ?

If the same activity could be perfomed either in a Emergency or a PhoneCall Mission.

May be :

Activities.fp5

Persons.fp5

Emergency_Missions.fp5

PhoneCalls_Missions.fp5

JoinFileMissionsPerPersons.fp5

This kind of settings would allow you more finesse even in your counts...

[Arttu Lahdenpera]

Hello !

Umm... Activities does not need it's own file, because it is included in those two files (activities mean: patient transported with helicopter or escorted by a physician etc. or received phone calls during night time and so on).

But how to build up that joined file ??

Arttu

[Ugo DI LUCA]

OK,

Let me try something here. Just hope all this will make sense.

Emergency_Unit_Mission.fp5

PhoneConsultations_Mission.fp5

Persons.fp5

Activities.fp5

(Patient.fp5)

Activities.fp5

Actvity_ID:Act0002

Activity_Name : Helicopter Transportation Escort

---------------------------------------

Actvity_ID:Act0003

Activity_Name : Night Incoming Phone Call

---------------------------------------

Actvity_ID:Act0004

Activity_Name : I.R.M.

---------------------------------------

Actvity_ID:Act0008

Activity_Name : Radiology commentaries report

Persons.fp5

Person_ID : Pers0006

Person_Name : John SMITH

Person_Function : Physician

---------------------------------------

Person_ID : Pers0018

Person_Name : Arttu Lahdenpera

Person_Function : Radiologist

---------------------------------------

Person_ID : Pers0125

Person_Name : Laura Bella

Person_Function : Psychologist

Emergency_Unit_Mission.fp5

Intervention_#:Int00005974

Intervention Date_07/08/03

Person_ID : Pers0006

Person_Name : John SMITH

Actvity_ID:Act0002

Activity_Name : Helicopter Transportation Escort

Patient_ID (from a Patient_file)

Report : Joined Memorial Hospital on Time

-----------------------------------------------------------------

Intervention_#:Int00005975

Intervention Date_07/09/03

Person_ID : Pers0018

Person_Name : Arttu Lahdenpera

Actvity_ID:Act0004

Activity_Name : I.R.M.

Patient_ID (from a Patient_file)

Report : Some text

-----------------------------------------------------------------

PhoneConsultations_Mission.fp5

Consultation_#:Int00005974

Consultation Date_07/08/03

Person_ID : Pers0125

Person_Name : Laura Bella

Actvity_ID:Act0003

Activity_Name : Night Incoming Phone Call

Patient_ID : Anonymous

Report : Stress

-----------------------------------------------------------------

I think you get it. This only may suits your need if you want to list all activities.

One step further. Don't read if you already are confused by the previous statements...

If more than one activity may be performed (and you may want to keep track of them) or/and many persons involved during an Emergency_Mission or Phone Consultations, then you'd need a join file this way :

Emergency_Unit_Mission.fp5

PhoneConsultations_Mission.fp5

Join File.fp5

Persons.fp5

Activities.fp5

(Patient.fp5)

The Emergency_Unit_Mission.fp5 and PhoneConsultations_Mission.fp5 would just hold these fields :

Emergency_Unit_Mission.fp5

Intervention_#:Int00005978

Intervention Date_07/09/03

Person_ID : Pers0009

Person_Name : Michael Jones

Many activities could be joined to the Intervention_# through a joinfile.fp5 that would hold these fields :

Intervention_#:Int00005978

Intervention Date_07/09/03

Activity_ID : Act0009

Activity_Name : Epilepsy Crisis Care

If many Persons may be performing the same intervention (like a chirurgical operation) and you need to keep track of all the persons and all their activities, then you'd need a many to many relationship.

Therefore, the Emergency_Unit_Mission.fp5 and PhoneConsultations_Mission.fp5 would just hold these fields :

Emergency_Unit_Mission.fp5

Intervention_#:Int00005979

while the rest of the data would be "stored" in the Joinfile, relating Many activities AND Many Persons. The Join would hold these fields :

Join# : 0000tf555sz

Intervention_#:Int00005979

Intervention Date :

Activity_ID :

Activity_Name :

Person_ID

Person_Name

--------------------------------------

Join# : 0000tf555sz

Intervention_#:Int00005979

Intervention Date :

Activity_ID :

Activity_Name :

Person_ID

Person_Name

...

[Razumovsky]

Hi Arttu,

Before getting into so much detail, think about the basic model of your database. What would be wrong with having 2 files

People.fp5

Activities.fp5

People would be your join file, so to speak. Activities would be shown in a portal in People.fp5 and have a field t_ActivityType that would be set at PhoneCall, Dispatch, HeliEscort, MDEscort or whatever.

If you keep all the activities in a single file, you will have much more flexibility in manipulating/combining info later on (I believe). Also, you will not be storing duplicate data in different files (your current model seems to be storring personal data in both activities db's).

Is there some specific reason why you need activities in different files?

-Raz

[Arttu Lahdenpera]

Ugo, Raz

Thank you both ! I start thinking whether to rebuild whole system (it took only 7 years to get it to this point ).

Tho database is built for our helicopter emergency medical services needs. Each record in "missions" -file is one mission containing all the crucial data for that spesific mission (response times, patient data, crew, addresses etc.). The other database is for consultation phone calls.

I use this database for calculating salaries too. The crew is paid extra for missions and phone calls during night time. There is also different kinds of missions, which are paid differently and must be separated. For this reason I have to use "Find" to extract these "activities".

When I first asked help here, I planned to do the find operation in missions -database and then use relations to show found set in another file as summaries sorted by doctors. But apparently it doesn' work

So far I have done this with scripts which performs search ja then exports those records to other files. It is very complicated and clumsy system, I know...

Arttu

[Ugo DI LUCA]

Hi,

We're getting close here.

You have a Many to Many relationships from Persons to Helicopter Missions and a One To Many relationships from Persons to Phone Calls.

In theory, you would solve the Many to Many with a Join file within Persons and Helicopter Missions, while there won't be any need for any other Join for the PhoneCalls database.

What remains obscure here is the "Activities" in se, and the kind of different tasks some would be attached to.

May be you only wanted a Attendee count....

You will be able to use relationships from the Persons.fp5 to the Join to have listed all "activities" in which a Person participated, and a single relationship Count to the PhoneCall. Using dates would lead to "select" the appropriate info for your stats, salary calcs and more.

HTH

[Razumovsky]

I start thinking whether to rebuild whole system (it took only 7 years to get it to this point

I know that feeling all too well!

I can understand the reluctance to scrap the whole thing at this stage and start over. It is a decision for you alone.

Perhaps you could get away with leaving the activities files as is and creating a third "People" file that would act as the merge.

People:PersonID::PersonID:Activities1

People:PersonID::PersonID:Activities2

you would essentially only need 1 field in this file (PersonID) and then pull all other data from the other files through portals and relatrionships. You could propbably still use all your existing scripts, triggered from people.fp5 instead.

This might take a bit longer than coming up with another work around, but might pay off the next time you had to do something similiar.

-Raz

[Arttu Lahdenpera]

Hi !

There still remains the original problem that I need to use several different "Find"-procedures inside "Missions"-file to get all different activities during night time for each person in crew.

These night time activities are:

Helicopter cancelled prior arriving to scene (=no patient)

Patient seen, but not transported in Helo nor escorted by MD to hospital

PAtient escorted to hospital by the MD

Patient transported in Helo to hosp

Received consultation call

Now I use a script to perform several different finds and always export found set to another file (i.e "cancelled.fp5", "escorted.fp5" etc.). From these separate files I can combine all activities to one table.

There must be an easier way to do this. Maybe you already explained it, but I'm a little bit slow...

Arttu

[Razumovsky]

How about creating a field c_Type that would be a calculation:

case(

Helicopter cancelled prior arriving to scene (=no patient) , "Night",

Patient seen, but not transported in Helo nor escorted by MD to hospital, "Night",

PAtient escorted to hospital by the MD , "Night",

Patient transported in Helo to hosp , "Night",

Received consultation call

, "Night",

"Day")

Then just run one find for "Night" in c_Type.

of course, you would need to identify the right condition for each of these as "Patient transported in Helo to hosp" wouldnt quite be specific enough.

a additional thought is creating a DateLedger.fp5 file that would have 3650 records (one for each day for the next 10 years...) with fields

g_PersonID

d_Date

c_MatchKey: g_personID & " - "& datetotext(d_Date)

then create relationships to similiar match calc field in both activities db's

c_MatchKey: Case(c_Type="night", t_personID & " - "& datetotext(d_Date))

you could then select the records in DateLedger for the dates you want the report for, enter the personID in g_PersonID and view the activities by date in 2 portals for each record using relationships-

DateLedger:c_MatchKey::c_MatchKey:Activities1

DateLedger:c_MatchKey::c_MatchKey:Activities2

Is that helpful at all?

-Raz