Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Find earliest date


Tacitus
 Share

Recommended Posts

Hi all,

 

I'm trying to build a script along the following lines:

 

Go to related table 'elections'

select field 'date'

find earliest date (year 1990 trumps 2000). This will most likely be in the first related record (but not necessarily) so go to first record could possibly be used if all else fails

copy earliest date

go to related record in table 'Individual'

select field 'first elected'

paste data (date)

 

Note that in both tables the record already exists, it is the field in 'individual' which is empty. The script would have to loop and move to the next record until all were exhausted. If it finds no data in table 'elections' it would need to paste a blank and then move to next record. Likewise if data exists before pasting (unlikely but possible) it should overwrite.

 

I was thinking this could either run manually or on closing the file.

 

Is something like this feasible? I'm a novice at this game so forgive me if it all seems obvious.

 

cheers,

 

Tacitus

Link to comment
Share on other sites

Well, you don't say WHY you need to do this. The whole idea of relational database is to avoid redundant replication of data.

 

So, to display the earliest election date of an Individual, you could sort the related Elections records by date, ascending (in the relationship's definition), then place the Date field from the Elections table on the Individual layout (a related field placed outside of any portal always shows data from the first related record).

 

If, for some reason, you need to keep the relationship sorted in a different order, create a calculation field in Individuals (result is Date) =

Min ( Elections:: Date )

Link to comment
Share on other sites

Thanks for the reply.

 

I'm a postgrad history student and the DBase is for a history project looking at local council representatives through time. Thus there are separate tables for the individual and their electoral record.

 

It seemed reasonable to have a summary table of the periods they represented a particular area (date first elected and final resignation date) as well as full details of their election results (votes opponents votes and so on).

 

However this means that the 'date first elected' appears in two tables - one would have data entry the other would be a paste of the date from a particular record. This is the tricky part, the summary sheet has to display a particular date from a particular record - generally the date in the first related election record.

 

It may be the summary field could be setup as show field 'date' in first related record in table 'elections'.

 

At some point I am going to have to do some standard sort/search queries such as length of council service and who they represented and when, and it may be this is a better way of displaying the info I want, "on demand" First date elected is going to be in the elections table anyway.

 

Unfortuately my Uni ditched FMPro in favour of Access following a contract they received from you know who. All the FMPro guys subsequently left.

 

Any advice gratefully received.

 

Tacitus

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use