Dynamic Select in Conjunction with Macro

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

DYNAMIC SELECT.zip

Macros

Accepted answers

danilang

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

DynamicSelectDynamicPrefix.yxzp

All comments

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

SelectColumnsAndWrite.yxzp

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"?

Select Columns And Write (Main).yxmd

binu_acs

@danilang excellent solution as usual!!

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

Select Columns And Write (Main).yxzp

TomBock

@danilang , @binu_acs :

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

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

DynamicSelectDynamicPrefix.yxzp

TomBock

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703