Summary of related record

[devinriley]

I have two tables, my master and a stage table. In the stage table, we're keeping track of the current, and historic, stage that the main record is at a given point. A new record is created each time it changes Stage, so it's one to many in that regard. The users now want to track and know how many of those projects are currently in stage A and be able to limit the ability to add a new project at that stage once the total is over 1000.

 

To do this, I created a calculation field with the following definition:

 

Patterncount(TargetStatus::Stage; "A")

 

Since the relationship is sorted in descending date order, it always evaluates the most current, and marks the project if it's in stage A. I then added a summary field that totals this calculation to get the number of my Stage A projects.

 

All well and good, and this database only has a few thousand records right now, but this is really slow waiting on the calculation and the summary. Is there a better way to set this up? I tried playing around with other relationships between the tables to try and eliminate the calc field, but I'm getting all the A's regardless of when it was from, so 1984 instead of the 997 it is.

 

Can anyone think of a faster, from a database workload standpoint, way to get this same data?

 

Then, are there any suggestions to limiting data entry once it hits that 1000 threshhold?

[Techphan]

I am not so sure I understand the "gist" of your database (but totally understand since I have a hard time explaining my solutions to even a knowledgable person), but I am unclear as to how these two tables are related.

 

Secondly, can a MASTER project have more than one STAGE A record? If not, then when you add another STAGE record then set the current status in a global field and run a search on that field to find your projects in any phase you desire.

[Maarten Witberg]

IIUC you limit the number of new projects that enter in stage A, so automatically there is a limit to projects in stage B, C and so on. But it seems to me that a project can only be in one stage at a time. So I guess it would be better to track the stage in the master record.

This would make it much easier to track the number of Stage A projects. All this said, you can still have the stage line items table to track all kinds of stuff, but again, not the current stage a project is in.

 

Then, are there any suggestions to limiting data entry once it hits that 1000 threshhold?

I think you need to script creating a new record in the master table. This would require creating a custom menu so the user cannot circumvent the script.

If you decide to track the stage in the master record, create a calc field StageA that is auto-enter "A".

then a self-join

MasterTable::StageA = MasterTable::Stage . call the new TO Master2Master

This would allow you to use a limit test in the new record script along these lines:

 

 

allow user abort [ off ]
set error capture [ on ]
go to layout [ Master (Master) ]
if [ count [Master2Master::MasterID > 1000 ]
  show Custom Dialog [ "limit reached" ; etc.... ]
  go to layout  [ original ]
  halt script
end if
new record / request
set field [ MasterTable::Stage ; "A" ]
commit records / requests
show custom dialog [ "project count" ; "the current count of projects in stage A is " & count ( Master2Master::MasterID ) ]
#more code as needed

 

or, if you want to allow the creation of a master record but not to enter the project in the first stage, then validation of the Stage field is an option. Something like

 

Case ( not IsEmpty (stage ) ; Count ( Master2Master::MasterID )

No scripting is necessary then. FWIW validation will delete a new, uncommitted record if you click 'restore' upon validation fail. So you might do without scripting anyway. Personally I'm not fond of validations in these situation as it is not very transparent to the user what is happening.

 

edited

[devinriley]

I am not so sure I understand the "gist" of your database (but totally understand since I have a hard time explaining my solutions to even a knowledgable person), but I am unclear as to how these two tables are related.

 

Secondly, can a MASTER project have more than one STAGE A record? If not, then when you add another STAGE record then set the current status in a global field and run a search on that field to find your projects in any phase you desire.

 

 

Technically yes, in practice I don't think it's happening. Things generally start at Stage A and then can move on to B,C,D etc. It could though go to stage B or C and then back to A if a problem occurs.

 

The Stage table is basically a history log. Each record there has a date it was created and a Stage, so that we can see and track when a stage changed and what it used to be. For the calculations, I don't want ALL the A records in that table as it would include old "historical" entries, I just need the ones that the most recent related record is A. I could further complicate this with a flag field that marks a 1 for a current stage and script it to be 0 when a new record is created, but I don't want to complicate things unless I know it will save the overhead. That would let me match a constant(1) to flag and my global(A) to Stage and get just the related count, but take more storage and script reliance.

[Techphan]

I can understand your reluctance if you have to loop through all of your STAGE records with a script (for example, I imported an old .dbf file with > 100,000 records, converted some date fields and populated some newly made others, and it took the better part of an NBA playoff game first quarter - 20 minutes - to accomplish) every time you want to run a search.

 

But I access (via ODBC) a file of more than 10^6 records and can run a typical search in less than 2 seconds.

 

I am not being critical here (just curious), but what "storage" and "overhead" are you worried about?

 

UPDATE: Re-read entire thread. You mentioned in your initial post that the current process works but is slow. How slow? Is you app is FM v6 or FMv8?