Join & Create New Record via Attribute Rule

JohnMDye — Sun, 12 Dec 2021 08:34:28 GMT

Consider the following Geodatabase Feature Class and table:

Accounts Feature Class (12 million records, updated monthly)

OBJECT ID	CUST_ID	FIRST_NAME	LAST_NAME	OPEN_DATE	GEOMETRY
1	0956732561	Jane	Doe	12/31/2017	Point
...	...	...	...	...	...

Transactions table (80,000 records, updated hourly with a 30 day window)

OBJECT ID	TRX_DATE	CUST_NUM	AMOUNT
1	04/17/2019 14:21:56	0956732561	2.31
...	...	...	...

Desired Result

TransactionsFC feature class (80,000 records, updated on the fly as Transactions Table is updated)

OBJECT ID	TRX_DATE	CUST_NUM	AMOUNT	GEOMETRY
1	04/17/2019 14:21:56	0956732561	2.31	Point
...	...	...	...	...

Now, I don't get any kind of geographic identifier in my transactions table. No address, no coordinates, no zip code, nothing. There is absolutely nothing to base any kind of geometry off of in that table nor can I get it.

What I need to do is create a new and separate feature class of the transactions which uses the geometry of its associated feature in the Accounts feature class. As a one time job, this is easily doable by joining the Transactions table to the Accounts Feature Class by CUST_ID and CUST_NUM and then exporting that to a new feature class.

There are a couple of problems that prevent me from doing that:

Update Cadence: My transactions table is updated hourly, with a 30 day window of transactions to the hour. So any record older than 30 days to the hour is deleted, any new transactions that have occurred within the last hour are added, and any records which have had updates are updated.
Runtime: Doing a join between a 12m feature dataset and an 80k record table takes an abysmally long time and given the update cadence, it basically turns into a never ending geoprocessing queue. By the time one job run has finished, there may be another one or two in queue that need to be performed.

So I'm trying to create an Attribute Rule on the Transactions table, that would do the following:

Requirement 1: Whenever a new record is added to the Transactions Table:

Get the Customer_NUM of the new record
Get the corresponding CUSTOMER_ID of the feature in the Accounts feature class
1. If there isn't a corresponding CUSTOMER_ID in the Accounts feature class, the transaction most likely is from a new Customer that was created within the last month which has not been added to my Accounts feature class. Do nothing.
Create a new feature in Transactions Feature Class, populating it with all of the attributes for the new Transaction record in the Transactions table, but using the Geometry from the corresponding Account feature in Accounts feature class

Requirement 2: Whenever an existing record is deleted from the Transactions Table:

Delete the corresponding Transaction feature from the Transactions feature class.

Requirement 3: Whenever an existing record in the Transactions Table is updated:

Update all of the attributes in the corresponding Transaction feature in the Transactions feature class, but do not change the geometry.

I started playing with attribute rules in Pro (version 2.4.1) and while these attribute rules would need to work in an Oracle Enterprise Geodatabase for now, I'm just testing in a File Geodatabase. I started trying to build an Attribute Rule on the Transactions table which would create new records in the TransactionsFC feature class as new records are added to the Transactions table (Requirement 1) but it doesn't seem to be working.

var transaction = $feature;
var transactionID = $feature.CUST_NUM;
var accounts = Filter(FeatureSetByName($datastore, 'ACCOUNTS', ["CUST_ID"], true), "CUST_ID = @transactionID");
// If no customer accounts found, must be new customer
if (IsEmpty(accounts)) {
  // do nothing
  return
}
// If one or more customer accounts found
if (Count(accounts) >= 1) {
  // take the first account feature
  var account= First(accounts)
  // return an edit operation
  return {
    "edit" : [{
      // on the TransactionsFC feature class 
      "className" : "TransactionsFC",
      // which adds a new feature
      "adds" : [{
        // with the following attributes from the Loss record
        "attributes" : {
          "CUST_NUM" : transactionID
        },
        // and the geometry from the account
        "geometry" : account
      }]
    }]
  }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The expression validates fine but obviously I'm missing something. Whenever a new record is created in the Transactions table, a new record is not created in the TransactionsFC with the appropriate attributes and geometry.

Any help is appreciated.

topic Join & Create New Record via Attribute Rule in Geodatabase Questions

Join & Create New Record via Attribute Rule