Skip to main content
  • EN

I'm reading in a single Access database with multiple tables that contain a common attribute, then outputting multiple Access databases via a fanout on that common attribute. It's quite a simple translation. However, I have an isolated, unrelated table that does not have this common attribute field, but I would like this table to be written (in its entirety) to each fanned out Access database. To illustrate this I'll provide an example:

Input DB

table name: State

StateAreaPopulationAZ14AK25CA36

 

table name: Roads

StateRoad_nameLengthAZa7AKb8CAc9

 

table name: Contracts

Contract_nameDescriptionthisetcthatetc

 

Output DBs - Fanned out by "State", but I want the Contracts table to be written in it's entirety to each.

AZ.mdb - contains the "State" & "Roads" tables (with just "AZ" records), and the "Contracts" table in it's entirety.

AK.mdb - contains the "State" & "Roads" tables (with just "AK" records), and the "Contracts" table in it's entirety.

CA.mdb - contains the "State" & "Roads" tables (with just "CA" records), and the "Contracts" table in it's entirety.

I've currently set up a Cloner to replicate the "Contracts" table a known number of times (copied three times in this example - one time for each "State" value). The "Copy Number Attribute" from the Cloner is then being re-named to "State" to allow for the file fanout, then the numeric (0, 1, 2) values are conditionally replaced with a valid "State" value in an AttributeManager. This seems to work fine, but if a new "State" gets added to the "State" parent table, then I will need to manually update the # of copies in the Cloner & add in another conditional replacement statement to the AttributeManager.

In short, I'm struggling to find a more dynamic solution, but I suspect this can be done by using list attributes. I've made an attempt at this but have yet to find a solution. Does anyone have any immediate ideas?

Thank you for any help you can provide.

You could try, putting your incoming features through a duplicatefilter to get a feature for each state, a list builder to build a list of each state, a featuremerger to unconditionally merge the list onto every requestor from the contracts table, followed by a listexploder to duplicate the features per state. You may also have to rename the attribute containing the state at this point

@bwasserstein

 

You can automate the proces wehre adding a table requires you to add a stat Contracts table by doing folloing.

 

Sampling the states from your featuretypes/tabelnames (Sampler) , 1st feature. Then count them with count (counter). Statistics on count to get max.

Merge the result unconditianlly to the Contracts table.

Use max+1 to clone the Contracts table (Cloner).

Now you can merge the state-values by merging on _count= _copynumber.

That way you transfer the state to the cloned Contracts tables, each its own.

 

Now you can continue using the dataset and featuretype fanout. Dynamic schema based on input files.

 

Added a sample.wasser.fmwt

Reply


Community Stats

30,159
Posts
114,480
Replies
37,805
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.