searching 2 fields for 1 result

[digidiva]

Hi. I am doing a single date search, not a range.

I would like to search 2 fields in 2 different related databases and have the single result placed in the main data base.

restated: I would like to enter a single date and search for all "followup appts" and all "initial appts" for the search date and have that information displayed on the layout "daily schedule", along with the corresponding time and appt type (initial or followup)

the structure: DB1= daily schedule DB2=followup appts DB3=initial appts

the appt type is a global field in both DB2 and DB3 where I =Initial and F=Followup

Currently, DB2 and DB3 hold the appt information. DB1 is where that information is entered and displayed on layouts with portals and relationships.

DB1 is where the search criteria will be entered and displayed.

I have all the elements, I just do not know how to stitch it all together.

Thank you.

[BruceJ]

In each appointment file, create a relationship to the a new third appointment/log file that allows the creation and deletion of new records - then - set it up so that when you create a new apointment in one of the two files, a script automatically sets the related mark field to "X" (This will create the related record in the thrid appointment file).

In the third file, create a calculation for each field that uses

If(IsValid( fileARealtionship: field..), fileBRealtionship:field.. ). This Calc will fill in the field with the right data depending on which file created it.

Also - when the real appontment is deleted or changed - it'll update the thrid linked file.

When you do a search, do it in the third file.

I'll try to make a quick sample and attach it.

Apptment.zip

[BruceJ]

Heres the same attachment if Stuffit Format

[digidiva]

Hi and thank you for your reply. the solution works nicely for single dates. what i need based on your example is this:

The initial appt date does not change. There is only one.

The followup appts do change, and, the history of the followups needs to be kept.

what i have in the DBFile Followup is a PORTAL that keeps the followup appt history up to 15 appts, that are sorted by most recent at the top.

that most recent appt is displayed on the mainAppt DB

The data entry for both the InitialAppt and the FollowAppt is done in the mainAppt DB

So, what i think i am doing is entering data in mainAppt and having that data stored in intialAppt DB and FollowupAppt DB, then I am entering a layout in MainDb and finding the appt dates, times and type from intialDB and FollowupDB and having that information displayed in a columnar report as you have done in your elegant example.

can your example be expanded to do that?

Thank you.

[BruceJ]

Sure. There's always more than one way to skin a cat, here;s another wiht what I think you're trying to do.

I work in the medical field so, I assumed that this is what you're trying to do. See the attached Zip

Appt2.zip

[BruceJ]

Here it is in StuffIt Format

[digidiva]

I have looked at your second example and am not sure about it. I am not sure why you need to mark the field to create the record. I Think it is beyond my mind right now.

I have attached an example file of the problem I am trying to resolve in the simplest form. Perhaps you can reveiw this and instruct me further according to this example.

thank you for taking a look.

helpSample.zip

[BruceJ]

OK, I had a look and aded a few things to make it work.

Before you get too far into creating things, I'd have to ponder why you have two seperate files for appointments? One file would suffice, the appointment type can be used in a combined key with the patient ID to create portals in the MainDB that either show Initial of F/U. -And you are able to maintain a much simpler and less cumbersome way of doing your searches and finds.

So - with that aside - assuming there's a compelling reason to have these two files - I've added a couple of calc fields to each of the files to facilitate data display in the MainDB list view. The Find actually uses a script to copy and paste (a no no if you can avoid it I know) into the Find Mode and perofrms two separate finds.

I did this in FMP6 which allows for some different options when performing finds eg expand found set, ect. If you're not using FMP6, you'd need to change this a little. Use the "Find" button to begin the find process.

Let me know if this is way off track or if you want to call me and I'll see if I can figure it out over the phone as well.

--oops --- forgot to mention - I changed the global search field to a "date" type from a "text", this forces the year and is also necessary in the calcs for the display process.

45368-helpSample_1.zip