Dynamic Select in Conjunction with Macro

[TomBock]

Hello:

I'd like to get some assistance with improving a *sample* process including a DYNAMIC SELECT.

Here's what I currently have:

- TEXT INPUT tool containing six fields

- Each of the fields include prefixes such as {FF, LK, Other}

- Now, for the 2 prefixes = {FF, LK) I have added a DYNAMIC SELECT each... followed by an OUTPUT (into the same MDB database).

Current Process:

- Upon executing the workflow, 2 tables (one for FF fields and another one for the LK fields) are added to the MDB database.

- Given that I do NOT have another 3rd DYNAMIC SELECT, the field [Other_Something] does not get output at all (as expected).

My question:

- I may have additional prefixes beyond "FF, LK, Other", so I do NOT want to add a DYNAMIC SELECT for each of the field prefixes.

- How should the workflow be modified so that all fields (including those that may contain a 4th or 5th prefix) are added into their respective tables?   Maybe via a macro?

Thank you,

Tom

 

[danilang]

Hi @TomBock 

I think you're right about requiring a Macro.  I can't see any other way to do this.

The main workflow uses a Field Info tool to extract the column, extracts the prefix for each column and summarizes the prefixes to pass to the Control parameter of the batch macro.

The batch macro uses the prefix in the control parameter to modify the Dynamic Select formula and also the Table name in the Output Data tool.

Dan

[TomBock]

@danilang -- hi -- thank you for the assistance.   This solution looks very promising.

I re-created your main workflow (see attached) but ended up getting an error on the batch macro.   See attached JPG.

What am I missing wrt the config of the control parameter (5)... where I chose "NAME"?

 

[binuacs]

@danilang excellent solution as usual!!

@TomBock No update is required to the group by tab, update the questions tab and select the Name field

[TomBock]

@danilang , @binuacs :

thank you for the continued support... I appreciate it.

Allow me to recap:

- The macro now executes w/o any errors.

- For testing purposes only, I added another field (e.g., "Test_XYZ") into the TEXT INPUT tool and a 4th table "Test_Table" is added to the MDB file... GREAT!!

However, in the MDB output, all tables (whether FF_Table OR LK_Table OR Other_Table OR Test_Table) contain only the same 2 fields: [LK_LASTNAME, LK_GENDER].  See attached JPG.

How would the macro have to be modified so that the respective fields (e.g., FF_CASE_NUMBER, FF_AGE, FF_Summary) would be automatically added into table [ FF_Table]?  Sample applies for all remaining fields where field prefix indicates into which table the fields need to be inserted.

Thank you,

Tom

 

[danilang · Accepted solution]

Hi @TomBock 

Sorry.  There was a typo in the replace string for the Dynamic Select in that it included an extra space.  It's fixed in this version

Dan

[TomBock]

@danilang -- PERFECT!!!!   Your solution works 100% as envisioned.   Many thanks!

@binuacs -- thank you for the comment earlier... it helped me w/ the error issue (macro wasn't executing).