Find Record Between to Upper and Lower Value Fields

[smithpuckett]

Hi! All,
 
I have two tables:

 

Global Fields                                        

gMCOID

gProviderTypeID

gCategoryID

gStartTime

gEndTime

gElapsedTime

 

Services

ServicesID

MCOID

ProviderTypeID

CategoryID

RequiredTimeLow

RequiredTimeHigh

 

I have a Layout to create a record in a third table (ProgressNotes). On this Layout I would like my users to enter a start and end time and have FM pull in data from the Services table based on the elapsed time and how it falls between the values in the Services Table - RequiredTimeLow and RequiredTimeHigh. The two tables are matched (=) with the first three fields in each table. I have tried to do this. 

 

gMCOID = MCOID

gProviderTypeID = ProviderTypeID

gCategoryID = CategoryID

gElapsedTime >= ElapsedTime

gElapsedTime <=ElapsedTime

 

I can't get this to consistently work properly. I think it is going to the first record in the Services table and pulling that in.

 

An example of two records from the Services table look like this:

 

ServicesID "296

MCOID  "23"

ProviderTypeID "2"

Category "4"

RequiredTimeLow "960"

RequiredTimeHigh ""2279"

 

ServicesID "298"

MCOID "23"

ProviderTypeID "2"

Category "7"

RequiredTimeLow "960"

RequiredTimeHigh "2279"

 

(The only difference in these two records is the Category and the servicesID)

 

An example of a record in the GlobalFields table looks like this:

 

ElapsedTime "1800"  (along with the match fields above which are already populated from a relationship to another table)

 

Any ideas would be TREMENDOUSLY appreciated. I just can't seem to wrap my head around how to do this.

 

Thanks!

Peggy

[comment]

Here's the (most) confusing part:

 

 

I have a Layout to create a record in a third table (ProgressNotes). On this Layout I would like my users to enter a start and end time and have FM pull in data from the Services table based on the elapsed time and how it falls between the values in the Services Table - RequiredTimeLow and RequiredTimeHigh.

 

If you want to "pull in data from the Services table based on [data entered into a record in the ProgressNotes table??]" - then what does this have to do with the GlobalFields table?

[smithpuckett]

I'm using the GlobalFields table to create a new record in the ProgressNotes table. The data collected in the GlobalFields table is passed (through a script) to the ProgressNotes table. I realize I could do this with a portal but I don't want my folks entering data at the bottom of a portal. 

 

My real struggle is whether I can make this work through the relationship as I have it set up. I don't understand enough about the logic of finding the one and only one record that I need.

 

Thanks,

Peggy

[comment]

I am afraid your goal it still not clear. It seems to me (but I could easily be wrong about this) that you want to establish a relationship between the ProgressNotes table and the Services table. And that in this relationship, each record in the ProgressNotes table could be related to several records the Services table. And (if I really stress my imagination), perhaps you want to show a portal on the layout of ProgressNotes, to show the related records from Services.

 

If this is correct so far, then you should explain in more detail what the relationship's criteria (predicates) should be. And I still don't see what role the GlobalFields table plays in this; the relationship between ProgressNotes and Services will only look at the data in those two table. It doesn't matter how the data got there.

[smithpuckett]

Thanks for trying to help. Here's some more information:

 

The Layout is actually on a table - Providers. The layout has a portal with one line only which has the GlobalFields. The user (Provider) chooses a client from a popup menu of his/her clients (this information comes from a join table - Clients and Providers). Once the user chooses a client the MCOID and ProviderTypeID fields are pulled in. The user will then enter the start time and end time they saw the client. I have a calculation on the ElapsedTime field which gives that information once the start and end times are chosen. The user also enters a category - again from a popup menu - from the Categories table. At that point I want the ServicesCode field to be pulled into the GlobalFields table. The records in the Services table are filtered by MCOID, ProviderTypeID, and CategoryID fields based on the relationship between the GlobalFields table and the Services table. I have the ElapsedTime (from GlobalFields table) related to the Services table like this:

 

ElapsedTime >= RequiredTimeLow and ElapsedTime <=RequiredTimeHigh (along with the other match fields)

 

I want the relationship to pull in the ServicesID for the correct record but it is not doing so consistently. If the user changes one of the criteria (category, client, or time) the ServicesID does not change or there is no ServicesID at all and it is not consistently pulling in the correct ServicesID. 

 

The Services table is not related to the ProgressNotes table, only to the GlobalFields table. Once all the information is gathered in the GlobalFields I will use a script to transfer the information to the ProgressNotes table. 

 

In a nutshell - I want to filter the records in the Services table by MCOID, ProviderTypeID, Category, and ElapsedTime so that it returns the correct ServicesID.

 

Thanks, Peggy

[smithpuckett]

Thanks for the help! Got it solved!