Schneider Electric GIS Tech Support

I have tried without success to automatically update my customer information table. My model uses a many-to-many relationship class to join my gis service location feature to a table that contains customer info like name and account number. I want my custinfo table to be non-versioned and read-only. Then I want to be able to just replace or update this table independently.

There seems to be no way of doing this because if I make it non-versioned, then arcmap won't create a relationship to it when I'm designing.

I'm wondering how others are linking in this kind of information.

Thanks,
Duane

I guess how you handle this is going to depend on what you want to do with the customer data. For us it is really just used as FYI information. We keep it unversioned like you and built a tool that pops up a form showing the customer info when the user clicks on a transformer. As long as the key field is maintained properly in GIS then they'll see the latest customer data from the unversioned tables. I'm not sure if that's along the lines of what you're looking for or not.

Jim,

Thanks for the reply. How do you build the relationship to your customer info. It sounds like you do not have an ESRI Relationship Class for this relationship in ArcCatalog. Is that correct?

Thanks,
Duane

You are correct, Duane. We get to the unversioned customer data using ADO code. The GIS table carries a key field (Location ID in our data) that ties to the unversioned tables.
Jim

Duane:

What ESRI Version are you using.

In 9.2 there are mutliple ways to define tables.

1. Table as Versioned (Write to Delta Table) (As in 9.1 and previous)
2. Table as Versioned (Write to Base Table)
3. Table as Non-Versioned

When I heard about the Non-Versioned Tables, and that you must change your edit pointers to edit them (Stop and start editing when editing between Option 1 and Option 3), got me thinking you might not be able to create or edit relationships as you explained.

What you may want is to have the table versioned with the option to write directly to the Base tables (Option2), thus be seen by all versions immediately, instead of defining the table as non-versioned.

Ian

Duane:

A couple of questions....are you creating your meter records through an AU upon placement of the service location? If so, you have no choice but to version your meter table. There are ways of dealing with updates at the command line to greatly simplify the process. You can create a version through SDE command line, set that version as active through a delivered SDE SQL procedure, run any number of SQL scripts and against it and then close it again. All of this can be easily scripted. The only manual portion of the process is to bring up the version in ArcMap for rec/post. Even this can be automated if you’re employing some kind of batch rec/post process.
If your customer table has a reliable foreign key to your service location table, you could simply join the two through the TOC. Be careful here though, you will need to make sure you are properly indexed and can match a single customer record to a single service location. It sounds like this may not be the case (ganged meter bases, perhaps). Performance may also be an issue if your tables are large (even if you are properly indexed).

I was able to provide a solution to this issue. I made my customer information table to be versioned (Move Edits To Base). Then I use a many to many relationship class to join this table to my Service Locations. Now I can replace my customer info table every night. I force the objectid to be my static key from CIS system. :D

Here's the SQL to fill my customer info table (SERVICEPOINT).

[i]INSERT INTO designer.SERVICEPOINT(OBJECTID, ServicePointID, CustomerName, MailingAddress, ServiceAddress, RatePlanCode, AccountNumber, MeterNumber, RouteNumber, MeterReadOrder, FranchiseCity,LABEL_ADDRESS1,LABEL_ADDRESS2,LABEL_CITY_ST_ZIP)
SELECT service_point_id, service_point_id, [NAME], mailing_address, service_address, rate_plan_code, account_number, meter_number, route_number, meter_read_order, franchise_city, label_address1, label_address2, label_city_st_zip
FROM designer.CIS_SERVICEPOINT;[/i]

I have seen some problems with this approach but have been able to remedy them by removing all rows from the a and d delta tables for SERVICEPOINT since this table should never be changed by SDE. I'm sorry but I can't remember exactly what the problems were. Its been working flawlessly for months now.

We have built a MS Access App that connects live to our Responder OMS and NISC CIS via ODBC. We pull in the data from the GIS and CIS, run through some data manipulation, and spit it out to the RX_Customers table. In essence we replicate our data from CIS to RX_Customers, wihile adding a Service Point OID to the record. The data manipulation really is our model transformation where we change domain values and field data type that are different between the models.

Once we built the transformation model, we run the access application on a MS Timer, usually every four hours.

Key points:
1. Our RX_Customers table is non-versioned, oracle table.
2. We use Multi-version views on our Service Points and associated Meter records within our GIS due to the fact these features are versioned.
3. We related 1 Service Point (feature) to many Meters (table) in our GIS.
3. We associate the MeterID to our meters within the GIS to create a FK/PK relationship between our GIS and CIS.

Although each user's CIS may be different, anyone who wants the Access DB to see the thought pattern we have is welcome to it.