Beginner/incompetent trying to link two databases

Morning all.

I’ve recently started using Panorama X to collate information about projects and buildings for our Architectural practice. I’m relatively comfortable with recording and manipulating the information in simple databases, creating forms, etc, but I’m rapidly approaching the limits of my intelligence.

I can see that I will need to create a series of databases that read from each other (nominally Projects, Buildings and Appointments). Each project can include one or several buildings and one or several appointment contracts throughout its life. Conversely, each Appointment could cover several projects.

Whilst I can see how this information needs to be arranged, and I can lookup the information from each database, what I can’t do is keep it in sync.

As a simple example: at the root of the records is a monthly CSV export from our practice management system called MIS_List, providing the latest figures on each of 3,000 projects, both new and historic. This includes fields for Project No, Project Name, Client, Status and Core Hours. This database will be kept very simple, with no embellishment, as it needs to be updated regularly with new projects and increasing hours (which will trigger various technical audits).

A second Projects database reads values from MIS_List by matching the Project No field, then embellishes with additional fields. However, it only looks for the external information when the Project No field is actively completed in the Projects database. When the MIS_List is updated, nothing changes.

How should I trigger this to update? Does it have to be done record-by-record, or can a database be kept in sync more easily? Should the information be pushed from the MIS_List on update, or pulled by Projects? I’ve been through the (excellent) help, but I’m struggling to find a clear answer.

Thanks in advance.

Based on your description, I would update Projects with a field-by-field FormulaFill, using the same values/relationships/calculations that you use in that database to set the values in the first place, using the ProjectNo field for lookups. This could be done by procedure, called manually, whenever you generate a new dataset for MIS_List.

Trying to get a handle on the timing/triggering you are asking for. It sounds like this is something you do monthly - with the new CSV export from MIS_List. Once you get the new CSV, you’d like a process to update all applicable records with one procedure. First - that’s probably doable. At this point I think you’ve mentioned three files; a source file that’s a CSV, a Projects file that you call a second database, and something that is the first “datebase” - is that MIS_list?

For simplicity - Forget about MIS_List itself. Start with a CSV (text) file. If it were me, I’d create a database that imports the CSV - replacing what records it had before - This CSVPan file holds procedures (processes) for updating the other database or databases. So the first step is to get the CSV into its own Panorama file.

From there, in procedure commands, you mimic the decisions you’d used to decide what records/fields you want to update. Note that “update” is a pretty broad term. It could mean adding, deleting, or editing records.

Does each record in the CSV (now imported into Panorama) address one and only one record in the file to be updated? Or could there be more than one update for the same record - different fields. If the same field in the same record would have more than one update in the CSVPan file, then you need to decide which of the two or more updates will be applied to the field.

For simplicity and lack of detail, lets say the records in the CSVPan file contain one of three actions, add, delete, edit, for some records in project.

When creating a database, it is useful to have a few extra “utility” fields. I usually add at least one text, date, and number field to the design. They come in handy when intermediate/temporary actions are needed.

I’m sure there is a faster way to do this but imagine selecting all the records in the CSVPan file that indicate deletion of records in Projects. Once selected, the procedure than uses whatever you have to match the CSVPan with it appropriate Projects record to … say, put an X in the spare text field of the matching project records. Then you select all records that contain an X, reverse select, and removed unselected. I’m leaving out details to check if none or all records are selected.

Next you can select all the records that need to be added to projects from CSVPan, The procedure then just imports those selected records.

Next, select all edit action records in CSVPan. In a loop you can go through them and using various “put the content of field X in database 1 into field X in database 2” actions. There are a few different ways to do that. Lookups, GrabData, etc. You could make the Projects file active and loop down it, record by record to see if the record has a match on the selected Edit records in CSVPan. But I’m guessing the number of records in projects is greater than the number of edits in CSVPan so it’s probably faster to loop though the CSV pan records at match on projects. Except the behind the scenes searching of projects might be cost more time then the smaller CSVPan - potato/pataatoh.

Then you can select all the records that need to be added to projects from CSVPan, The procedure then just imports those selected records.

In that order, you’ve made the projects file the smallest it can be - after deletes and before adds - to speed the searching for editing.

There is housekeeping - clearing any content in the utility field you used to “mark” a selected record. And there are finesses - reporting how many records were deleted/added/changed so you can see if the number of actions are reasonable.

And of course you want to make a copy of projects (part of the procedure) BEFORE you change it just in case there’s a … surprise. You can include “fail safe” checks so that if the update process was accidentally run twice, you wouldn’t duplicate the Add action. The first Delete action would have removed its matches so it would find nothing. The Edit action would just refill the same changed fields.

Hopefully the above gives you some ideas.