Possible?? - Newbie

[kiwiora]

I have no idea if this is possible or not....

I have several separate databases - 1 for each leasing transaction type.

I have to generate status reports of every record from every database.

So basically I need a new database which has additional "commentary" fields, and lookups from one to many relationships?? for the matter number (i.e you insert a matter number and it copies the relevant information from the database. The matter number is unique and never used twice.

So I guess the question is as follows:

I am in the transaction database, I have just created a new record. Is there a way where a record gets automatically added to the status database (maybe this is a script "update" which adds the records from the various databases on execution in the status database). It would need to "ask" if the matter number already exists. If not then it would proceed to copy.

I don't even know if this is at all possible. And I'm sure i haven't explained it as well as I could, but any ideas for some sort of solultion would be welcomed!!

[CobaltSky]

What you are asking about is certainly possible.

How you might go about it would be to script the update sequence, starting with a test such as:

1.[color:"white"].....If ["IsValid(StatusDb::MatterNo)"]

2.[color:"white"]........Show Message ["The current matter no already exists in the Status db"]

3.[color:"white"]........Exit Script

4.[color:"white"].....Else

5.[color:"white"]........Set Field ["StatusDb::FirstDataField", "FirstDataField"]

6.[color:"white"]........Set Field ["StatusDb::FirstDataField", "FirstDataField"]

7.[color:"white"]........Set Field ["StatusDb::FirstDataField", "FirstDataField"]

....................etc

n.[color:"white"].....EndIf

However I wonder if you really need to transfer the data in this way, or whether you might not be better to simply rerference the data within the transaction files using calculations within the Source file.

How that would work is that you would create relatiobnships from the matter number in the source file to each of your transaction files, then, for each of the content fields, set calculations with formulae along the lines of:

Case(IsValid(TransFileOne::MatterNo, TransFileOne::DataField,

IsValid(TransFileTwo::MatterNo, TransFileTwo::DataField, TransFileThree::DataField)

That way, the original data all resides in your transaction databases and is simply 'visible' from the status database, so that the only data actually stored against the matter numbers in the status database would be the commentary fields etc.

There are still other ways to approach it. However based on what you are saying, it sounds like one of the above might be a possibility - but you'll need to consider all your requirements and determine what suits best.