Finding From Multiple dBs

[LaRetta]

Hi Everyone!

I need to generate Contract balances. I have a Contracts dB which holds ClientID, ContractID, PayerID and the Max$ per Contract. Contracts also contains a FromDate and ToDate. I have a Service dB which contains services performed (per Client), during the Contract period (FromDate, ToDate). I also, of course, have a Clients dB.

I need to create a report. This report is to display the balance remaining on each Contract. However, I want to find only certain Contracts (Payer LaneCare) and Contract::FromDate > 9/30/2002. While in Clients, viewing Contracts from a portal, I've tried entering the portal and finding only the appropriate Contracts, but it still displays all Contracts for the Client instead of only the requested Payer. In addition, how do I find only Services relating to the Contract timeframe and Payer I'm looking for?

And how in the world do I display this report and from which dB? I want the report to sub by Client, with the Contract Max$, less Services provided. My previous *solution* was to search Contracts and export the required Contracts, then go to Service and export the services within the daterange. After merging them, I have what I want ... a running Contract balance per Client. What a funky way of doing things!!

I believe that a relationship can be used to filter Service and Contracts, but it would have to take into account Payer-LaneCare and a date range. I'm considering using a global to hold a FromDate & ToDate but this won't relate to Services. If anyone can figure out what the heck I'm talking about, do you have any suggestions?

LaRetta

[Razumovsky]

Hi LaRetta,

I think you have answered most of your question in the post itself:

I want to find only certain Contracts (Payer LaneCare) and Contract::FromDate > 9/30/2002

in your client file, create 2 globals (g_PayerName, g_ContractFromDate) and a c_Calckey: ClientID&"-"&g_PayerName

In your contract file, create 2 fields:

d_GlobalContractFromDate (this is just a plain date field)

c_RelateCalc: case(d_date>d_GlobalContractFromDate,ClientID&"-"&t_PayerName, "")

The user would type in the g_PayerName and g_ContractFromDate at the top of a portal based on:

Client-c_CalcKey=c_RelateCalc-Contracts

you would have to run a script or lookup to get the date from g_ContractFromDate in cclients to d_GlobalContractFromDate in Contracts, but that shouldnt be too difficult.

That is just to view the contracts though. For printing, I would do it from the contracts file. You could put a portal to services displaying the related contractID::contractID records (using the same type of relational filter for a date range).

Then just sort the records by client/contract and print a subsummary report that has the portal to services on the body of the layout. (Make sure that you make this portal ridiculously tall and use sliding to hide the space. If you have more services for a contract than portal rows, you will drop some from the layout.)

HAve to run- I know twas not the best explanation, but will check back later.

-Raz

[LaRetta]

Hi Raz!

Thanks for the clearer perspective! Yes, I knew I could find Contracts but that didn't take into account Services by Payer Contract DateRange. I wanted a relationship to eliminate the need for calcs or scripts but also grabbing only DateRange-related records from Service was my sticker! But then I remembered I have Mikhail Edoshin's SmartRanges in Services now which is being used to identify billable services, so I can use it to relate Services to Contracts via Payer and DateRange.

LaRetta