How to fake a relational database within Notes

Save new values in the QueryClose event to a new doc, and then you can use an agent to fetch and update appropriate "dependent" docs, like you would in a relations database.

View member feedback to this tip.

Building on my last tip, A better way to track field changes, if you save the changed fields to an "agent_initializer" document, you could trigger an agent -- which then could update related documents to the ones that has been saved.

Think about customer data -- i.e. addresses. Those addresses are inherited at creation onto other documents (invoices or whatever). Then the customer data changes. How can you update those invoices with the old addresses?

If you have used the technique from the other tip (with the list of fields and its values), you can save the new values in the QueryClose event to a new document, which you can then fetch with an agent and update appropriate "depending" documents (like those invoices) later.

MEMBER FEEDBACK TO THIS TIP

I have created similar functionality by writing the unique ID of the documents to each other, and if one is changed, it "synchronizes" with the other, based upon that document ID. This does not need to be a one-to-one relationship ... therein lies the "relational" functionality.

For example, I have a database for Engineering Change Requests (ECRs). I also have a database for Engineering 713s. Multiple 713s can be created off one ECR. In order to create a 713, it must be done from an ECR. So the first thing that happens when that 713 is created: the uniqueID of the parent ECR is stored in a hidden mutli-value field in the new 713. When the 713 is first saved, its unique ID is written back to a hidden field on that parent ECR. The ongoing status of the 713 is written back to that ECR, by ID, every time it is updated. All 713s are eligible to have a relationship with ECRs until they are marked "Complete." If the 713 is marked complete, that status is written back to the related ECR. If an ECR is marked complete, that status is written to all existing related 713s.

When at least one 713 has been sucessfully created, an option becomes available in all new ECRs, a button, labeled "Create Relationship with Existing 713." This button lists all existing 713s that are not marked complete. When one is selected, the ID of that ECR is appended to that multi-value field in the 713. A button exists in the 713, "View Listing of All Related ECRs."

Thus, you have complete relational functionality, using only one hidden field in each document, and one view in each database that categorizes by the document unique ID's. It has been live for two years now, under constant heavy usage, without a single complaint or bug :-)

—Andy Y.

******************************************

This tip is very short on details that you would need to actually do this. There's a basic conceptual flaw, in that Notes' replicated application architecture means that at the time a document is modified, not all related documents necessarily exist in the replica where the modification happens. A mechanism would need to be in place to continue to evaluate all new and modified documents to make sure their fields, which are copied from other documents, are up to date.

An example scenario: Database contains "Customer" records and "Order" records. The "Customer" records contain a phone number (which you can edit). The "Order" records contain a copy of that phone number, for the customer who made the order.

At the home office, someone updates the phone number in a Customer document. In all existing Order documents for that customer, the phone number is updated.

Meanwhile, using a local offline replica of the application, a field sales person is entering a new order for the same customer. Since the person is offline, she has not received the update to the customer record, so the old phone number is automatically filled in. That evening, when she returns to her hotel, she dials in and replicates the application, and the new order is written onto the server, with the old phone number. Because the update agent has already run for the changes to that customer record, there is no process to update the new order record with the new phone number.

Because of scenarios like this, keeping multiple copies of data should generally be avoided. Unless there's some strong reason to go to all this trouble (e.g. if the phone number must appear in a view of Order records), it's a lot easier to use a Computed for Display field with an @DbLookup formula in it, to fill in related fields in real time based on a key value stored in the related document (e.g. customer number, in this example).

Andre Guirard

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip exchange by member Michael Zischeck. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.

Dig Deeper on LotusScript

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchContentManagement

Close