Find and list non-unique values from a portal

[Rramjet]

I have a database that records patient's medications in a portal.

Field order = Medication, Dose, Date_1st_prescribed, Date_discontinued.

A patient may be on multiple medications in the one category (eg: sedatives or antidepressants, etc and I have a number of portals, by medication category, stacked in a layout to handle this).

I have two queries.

First, how can I find and display specified portal values for a single patient?

To clarify: Patient X might be taking three medications in the "anxiolytic" category portal

(but the portal for reasons of space and usability displays only two rows).

Over time the patient may discontinue a specific medication, but then at a later date,

restart it.

This would entail another entry of the same medication into the same portal (perhaps with different dose and perhaps separated by other medications -rows)but certainly with a different start date and discontinued date.

To determine a specific medication's history, rather, than scroll through the portal manually and note by hand each occurrence of the

specified medication and its dose and start and finish dates, is there a way to search for and list that specified medication (and related data) and display it (and it alone) in all its occurrences?

Second, is there a better solution to the way I am recording the medications?

Cheers -

[NovaChan]

Hi Rramjet. Getting your portal filtered may be easier than you think. Don't let the table relationships intimidate you (as often is the case with new/hobbyist developers).

It sounds like you're on something like a Contact table looking through at a related table occurrence (TO), ContactFieldOrder. What you would do for filtering the portal is add another TO like ContactFieldOrder_Filtered and base your portal on that. The filtered TO will be related using the same predicates as the non-filtered TO, but with one addition. It will probably look something like this:

Contact::primaryKey = FieldOrder::ForeignKey,

Contact::FilterMedicationName_Calc = FieldOrder::MedicationName.

Your new field, FilterMedicationName_Calc gets its value from another new field, FilterMedicationName_Global. The global field can go on your layout near the portal. When someone selects it, a drop-down list should appear showing all the Medications for the current Contact/Patient. Select one Medication, and the portal only shows entries with that Med. Clear the global field, and all of the entries display again.

The tricky part getting all the Meds to show in the portal when FilterMedicationName_Global is empty. That's what the Calculation is for. This is it's job: 'If there's a Medication assigned, show that medication name. But, if the global field is empty, show the name of every Medication for this person.'

Hope that makes sense. I found a demo file from Kevin Frank & Associates that shows this technique. You can download it here: http://www.kevinfrank.com/download/simple-portal-filter-demos.zip. Once you get the hang of this, you can add as many filter fields as you like. You just have to be sure that leaving the filter empty doesn't break anything.

There are multiple ways of filtering portals, but this seems to me to be the easiest for what you want to achieve. I'll list the steps to creating this below. Change the names to fit your own conventions:

1. Add a value list, MedicationNames, that shows all values of ContactFieldOrder::MedicationName that are *related* from Contact.

2. Add a new field in the layout basetable: FilterMedicationName_Global

3. Add another new field, FilterMedicationName_Calc, with the following definition:

If ( not IsEmpty ( FilterMedicationName_Global ) ; FilterMedicationName_Global

; ValueListItems ( Get( FileName ) ; "MedicationNames" )

4. Go to the Relationships section and create a new TO like the one described above.

5. Add FilterMedicationName_Global to your layout with a drop-down list showing values from MedicationNames.

6. Change the portal and portal fields to reference the filtered TO.

Question 2:

This response is a bit longer than I intended, so I'll leave question 2 to someone else. The main issue I see here is which values you want to put in their own tables. For example, medications should probably be in their own table. That way you don't have a problem if a medication name changes down the road or if you need to add fields that describe that medication. It also saves you from the worry that someone will misspell a medication name because it's being selected from a value list rather than being typed in.

The Category is another thing that could go into its own table. But, if the categories aren't going to change and you don't need to add any extra fields describing them, you might get away with leaving it in as just a field. Just be sure that, if you leave it in, people can only enter the value from a value list. That way you don't need to worry about misspellings.

Happy FileMaking :

[Rramjet]

Hi NovaChan -

Thank you for your extensive reply. I appreciate it. I will have a look at the "fast summary" option you suggest.

One other note of clarification is that I do have drop-down lists for medications, so no worries about misspellings.

I also think (now that it may be better to have ALL medications listed in just one portal with a "category flag" field included - rather than different portals for different categories.

I will let you know if your suggested solution works.

Thanks.

[Rramjet]

Okay, I now have the solution...

I have created a separate Table with the medications fields in it (Med_Category, Med_Name, Med_Dose, Date_Initiated, Date_Discontinued) and joined it to the Main table via a serial number (SRN).

I then created a portal with the variables in a row. Sorting by Med_Name. I also conditionally formatted with a self-reference the Med_Category Variable to a different font color for each category. Then using the Med_Category field as the reference, I conditionally formatted the other variables in the row to match the colours of Med_category.

Then I created a report "Medications by Patient and Category". With Header followed by a subsummary by SRN (with Patients name field also here) followed by another subsummary (by Med_Category) then a body with the remaining variables.

It works fine for my purposes.

Cheers,

Oh... I also created a tiny "Delete Portal Row" button in the first row of the portal (far-right top corner...), This seems to work MUCH better and more efficiently than scripting a delete portal row solution...

Edited June 24, 2009 by Guest

[NovaChan]

Glad to hear you're happy with your solution already. Those subsummary reports can be tricky too!

If you find yourself with time for another challenge later, you might try adding buttons to the portal column headers to get them to sort the portal rows. That is, if you're not doing this already.

Cheers

Edited June 24, 2009 by Guest

[Rramjet]

Um yeah...

Actually when I set up the portal I specified it to be sorted by Med_Category and Date_Initiated... which groups the medications by category in the portal automatically after data entry (and the conditionally formatted colours distinguish the categories from each other nicely) - so a sort button is then redundant.

Thanks for your input NovaChan - I do believe I have a use for the other solution you pointed me toward too! So a win all round.

In appreciation, Rramjet. :thumbup: