Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How make a search through a calculation field?


marysilvaramos

Recommended Posts

Dear All:

 

I have a table which hold the followings fields

 

PatientID,

Date

Weight

 

I need to find the last weight of the patient. The problem is not always the weight is entered (some records have the weight ="").

 

How can it be done though a calculation?

 

Thanks very much,

 

Mary

Link to comment
Share on other sites

Can you explain your self better? What do you mean by the last weight of a patient? Do you have more than one entry per patient?

 

If you want to separate the ones that are empty from the ones that have numbers, you might as well just sort the records by the weight field and omit the ones that are blank. Let me know if this helps, otherwise, reexplain what you are trying to do.

Link to comment
Share on other sites

No problem, Mary. To find the most recent weight entered, I suggest that you:

 

1. Create a stored calculation field called 'cLastWeight_key' of the same data type as your PatientID field, with the formula specified as Case(Weight, PatientID)

 

2. Create a self-join relationship (ie one that links the file to itself) called 'PatientWeight' which matches the PatientID field with the cLastWeight_key field, then define a descending sort on 'Date' for the relationship.

 

That's it. Now all you need do is to place a copy of the weight field, sourced via the PatientWeight relationship, on your layouts and it will always display the most recent weight entered for the patient.

 

Or alternatively, if you need to use the most recent weight in a calculation, the expression PatientWeight::Weight could be used within a calc formula. cool.gif

Link to comment
Share on other sites

Thanks Coolboy & Ray for answer my email.

 

I would try to explain better this time.

 

The patient can have more than 1 record and the most recent record not necessary has the weight entered, ie,

 

PatientID | Date | Weight

 

1 | 05/13/03 | ""

 

1 | 04/28/03 | 56.3

 

1 | 03/30/03 | ""

 

1 | 03/01/03 | 53

 

So, the issue is find the most recent weight through a calculation (omiting the blanks)

 

Thank you very much for your help.

 

Mary

Link to comment
Share on other sites

Mary,

 

What Ray suggested is the solution even after the second explanation. Create a calculation field and sort the data descending. Place the field on your layout and you are ready to go. If you are not too familiar on how to do it let us know.

 

Julio

Link to comment
Share on other sites

hello,

I have tried your solution, Ray, but I always get the same weight from the oldest date for each "patientid".

 

And I have read the solution again and again.

I don't know if you can guess what I am doing wrong?

 

Curious and interested

 

Thanks

 

Gaetan

Link to comment
Share on other sites

Hello Gaetan,

If your implementation is producing the oldest date, rather than the most recent, that would indicate that you have specidied an ascending sort on date (or perhaps no sort specified) for the PatientWeight relationship).

 

If you change the sort specification for the relationship to *descending* on date, as per the second point of my original post, I think you'll find it is a different story. wink.gif

Link to comment
Share on other sites

Right now you probably have a relationship:

 

WeightEntry_by_PatientID
Patient.fp5 ::WeightEntry.fp5
Key_P ::KeyF_Patient

This relationship links to all weight entries for a patient. It sounds like you need a relationship that only returns entries with a valid weight. To do this, create a calculated key in the WeightEntry.fp5 file:

 

WeightEntry.fp5
KeyC_Patient_with_weight
Case(

not isempty( weight ),

KeyF_Patient

)

Then create another relationship based on the new key:

 

WeightEntry_by_PatientID_with_weight
Patient.fp5 ::WeightEntry.fp5
Key_P ::KeyC_Patient_with_weight

 

This second relationship will only return entries that have a weight. To view the most recent weight entry, sort the second relationship by Date (descending). Now all you have to do is put a field on your layout that references the related weight field via the second relationship (WeightEntry_by_PatientID_with_weight). The field does not have to be inside a portal, btw.

 

Good luck,

Mariano

Link to comment
Share on other sites

Well Mariano,

It's clear that you can read.

 

But I'm not sure why you felt it was necessary to paraphrase my original post and re-post it... ?!! confused.gifsmile.gif

Link to comment
Share on other sites

Calm down, Ray.

 

Based on Mary's post (May 14, 2003 08:33 AM) it seemed she didn't grasp your explanation.

 

Based on Gaetan's post (May 19, 2003 12:54 AM) it seemed he missed something from it as well.

 

BTW, sorry... I didn't mean to paraphrase you. You give me too much credit when you write that it's clear I can read. I only skimmed your post and realized right after I posted that I'd pretty much said the same thing you did. :eek:

 

-Mariano

Link to comment
Share on other sites

Hey, we are all just trying to give and share a little help here.

 

Lets not lose sight of the fact that it doesnt matter who says what or how many times it is said, the advice is always welcomed by those who asked it.

 

And sometimes a slightly different post can give the light the questioner seeks.

 

You gotta admit ray, Mariano's post was pretty fly. laugh.gif

 

And if it was me, I would have a swelled head if someone 'paraphrased me wink.gif '.

 

Like I said, having it laid out differently and might I say so very professionally may give the answers to those who sought it.

 

Heck, having my name at the top of this great forum gives me a swelled head every single time I see it!!!! laugh.giflaugh.gif :eek: laugh.giflaugh.gif

 

Just my two cents, for what they are worth.

 

So, who's going to Devcon this year?????

Link to comment
Share on other sites

Hi,

That's cool. I was not uncalm, just perplexed... :rolleyes: wink.gif

 

I guess that, as Andy says, with several explanations to choose from, one of them *has* to be clear! smile.gif

 

Andy I'd guess that the answer to your last question is probably that *you* will be (going to DevCon)??! At least you're on the 'right side of the Atlantic' for it this year!! smile.gifsmile.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use