Problem - Relationships used as Filters

[Bobbby]

Hello,

The core of the problem is explained after the green text. You may prefer to skip that part as

it's the background to the problem where I explain the local restrictions and my preferences that limit the available solutions.

I have a problem.

I hope it can be resolved through relationships, because they're fast.

Where I work, an external company is developing our FM solution. They are off site.

They have the base and upload it to us at intervals. So we have a copy of the solution on our server here. I am on site and I have to create reports. So I keep my work locally in FM files and don't interfere with "their" files. Reports are separate from the dev.

[color:green]My options are restricted because I'm working on a local file with relationships to the base tables and I don't have access to the base tables to modify them. I physically do have access but we have an agreement not to meddle with each other's tables. It's like I only have a read access to their tables.

I have seen some developers stringing numerous tables together and scripting finds and "fast" summaries... Generally, these solutions freeze up or base and take hours to complete, if they ever do. These guys LINK before they FIND. I have seen solutions that link A to B to C to D to E... where B has 2 million records and then do a Find on A that produces a Found Set of 1000! So I start the other way round. Whenever possible, I FIND first, then I LINK.

I analyze the criteria of the report and start my script by selecting the records that will minimize the workload.

I open A, do the Find, Export to a temp_A (to get summaries), Import from temp_A into select_A and THEN LINK select_A to B to C to D to E. AFAIK, that technique reduces the work load massively.

So, I prefer creating a separate file that contains my script, and a subset of the data that I am going to work on. I call this separate file my SelectFile. or "s_" file. This is my local file, independent of what the developers are working on.

So, if Customers is a table in the overall solution then s_Customers would be my local reduced set of Customers resulting from a Find and Import, containing basically the Customer ID and any summaries.

Thus s_Customers is not only "shorter" because it has less rows, it is also "thinner" because it has less columns. And, once I have my FoundSet, I'm no longer using Customers and I'm not penalizing other users who are accessing Customers on the LAN.

The advantages are that

1. My relationships are based with a reduced record set, and

2. I'm not interfering with the off-site development.

The disadvantage is that I can only modify my on-site s_Customer file. I can't add or modify fields in Customers. I can add only modify fields in s_Customers.

So, this where I'm stuck...

I have three tables:

s_Customers ( 28000 records)

Payments (2500000 records)

Products ( 52000 records)

s_Customers links to Payments via Customer ID

Products links to Payments via Products ID.

s_Customers is a table, that gets filled by an import.

Fields: Customer ID, Customer name, ProductTypes (Global),...

Payments is the payments history.

Fields: Payments ID, Customer ID,

Article ID, Date, Amount ...

Products is a list of Products that customers may purchase or have purchased.

Fields: Products ID, Product Name, Product Type,

Now, if I import a series of Customer IDs into s_Customers, these ID will connect to Payments and Payments will connect to Products.

I can see what articles were purchased by my reduced customers set.

I know how to do that, it's working fine.

** I tell myself that my s_Customers table is "filtering" the Payments table.

But, now I have to refine my selection even further.

I have to find a way to select only certain Product types.

I have to select type Product types "Alpha" and "Gamma" but not "Beta".

My idea was to have a global field in s_Customers containing a list of the desired Product types

Alpha

Gamma

** Now I want this global field to filter payments too.

But this global field can not directly link to Payments as the Payments do not contain the product names, only the Product IDs.

Is there some way to do this without modifying the tables in the base? In other words, by only modifying the relationships in my local s_Customers?

TIA

Bobbby

[bcooney]

Many points for one of the best written post I've seen! However, I don't see a way to isolate Payments for a particular Product Type without being able to modify Payments. If you could just have a Product_Type lookup field in Payments (it needs to be stored since it is on the child side of a relationship), then you could have your Product Type global in s_Customers, and add it to your s_Customer to Payments relationship.

[Søren Dyhr]

Export to a temp_A (to get summaries), Import from temp_A into select_A and THEN LINK select_A to B to C to D to E. AFAIK, that technique reduces the work load massively.

This is not necessarily true - the only thing you need to mirror in your side of the matter is the record ID's, and since we're talking about summaries wouldn't "dead" linkings not be much of a problem, as long as the ID'ing not are following some of the more crafty schemes, but simply are serialnumbers, then make a table of immense size for each of the tables you're about to make summaries in.

Link the ID of each of you local table to the read-only restricted table via one-to-one relations, when ever a remote keyfield builds relations grap it via a calc'field and make a similar relational link in your end. Here in the one-to-one could you store the values retrieved by the fast-summary algorithm as well, since you hopefully not are stranger to shown summary fields are likely to drag the evaluation of values?

--sd