Need to identify *candidate* fields (from target system) based on legacy fields (source system)

[skydivetom]

Vlad:

This current project initiative (field mapping) is coming together really, really well!!

Although it's probably taking me a bit longer than anticipated, I'm very happy with the correct status. Attached is the latest version.

The only thing (as far as I can see now) that could be improved is the record navigation itself. Allow me to expand.

Sample File (attached):
- This sample file only contains 9 records (source table) and 22 records (target table).
- Based on the up-to 3 keywords (F1, F2, F3), query "qry02_Approach#1_Results" returns a relatively manageable number of matches... that is, 57 records that require screening/evaluation.

This is easily doable in the current setup.

However, I'll expand on the actual data set:
1. Source table includes 108 records (data elements to be mapped).
2. Target table includes 532 records (with fields to choose from).
3. Results query returns exactly 2,500 records (based on 3 keywords) that require screening.

Current Navigation:
- Compared to the XLSX, the Access DB works already 100 times better giving that I merely have to advance between records 1 and 108 **WITHOUT HAVING TO CLICK ON EXCEL FILTERS**.
- The latter is really great and will make a huge difference.

Here's the only challenge:
- If I wanted to go directly to record where [SECTION_NUM] = 4 and [FIELD_ELEMENT] = 3, I'd have to advance to record # 54 (out of 108).
- And currently, there's a slight delay when navigating across the 108 records.

Here's what I need to investigate and hope to change:
a. On the mainform, replace the first three controls ([SECTION_NUM], [SECTION_REF], and [FIELD_ELEMENT]) with either a combo/drop-down or list box each.
b. So, rather than having to use the navigation button, I would like to e.g., select "4" from [SECTION_NUM] and "3" from [FIELD_NAME].
c. Based on that selection, I should jump directly to record #54 (which then could also be hidden in the mainform's navigation button).

Do you happen to know of an easy solution where I could use combos/listbox to facilitate record navigation?

Cheers,


Tom

[Gicu]

Tom,

The easy solution is usually called "cascading combo's search" and you want the controls to be unbound as if you have them bound you will always edit the first record.

I will have a look at the attached file a bit later and make the changes, give me a few hours.

Cheers,

[skydivetom]

Vlad -- that sounds very promising.

The sample file includes the 3-tiered cascading scenario. Just to be certain, I filtered for the max values (in actual data set with 2500 results in query) for each of the "parent", "child", "grandchild" IDs/values. Picture is worth a 1000 words... see attached JPG for additional info. Hope it helps.

As always, thank you for your continued willingness / kindness to assist in this matter.

Cheers,
Tom

[Gicu]

Hi Tom,

Here is an updated file with the Matches label fixed and the three cascading combos for navigation.

Cheers,

[skydivetom]

Vlad:

THIS IS SUPER COOL!!!!

I can't stop staring at it and keep on smiling.

This tool has all the bells and whistles in order to efficiently find the "needle in the haystack". The MATCHES helps will more accurately honing into the top contenders.

The cascading navigation process is fantastic! I really speeds up the process going to a specific section (e.g., in the middle of the data set). Once I picked the value in the 3rd field, I automatically jump to the appropriate record. It's still good to have the command button as well.

Finally, having the "MATCHES: (n) records" looks much cleaner.

So, besides having a clean interface, all functionality far exceeds the previous process (i.e., exported the Access results query into Excel and then having to navigate through all the filtering).

MILLION THANKS TO YOU!!!

Cheers,
Tom

[Gicu]

Tom,

You're very welcome, now you and your team get started with the real job... actually using it to do all that matching .

One extra improvement you could add would be a toggle button to cycle the main form between showing all records in the tblSource vs. just the one remaining to be matched (all four candidate fields are null).

Cheers,

[skydivetom]

Vlad -- I like the idea of the toggle button. I'll look into it.

Additionally, out of the actual 108 field elements to be matched (and there will be substantially more for a different migration module), there is a subset of records where none of the keywords were matched up with any available target field(s).

Obviously, this is due to a poor choice of keywords. Well, in actuality, it's not really a "poor choice of words" given that text delimitation was used to auto-identify keywords that are part of the legacy fieldname.

For example, fieldname "Funds Required" -- with [F1] = "Funds" and [F2] = "Required" -- did NOT get matched up with any candidate target fields.

There could be 2 reasons for that:
1. The obvious one... neither keyword exist in the full set of 500+ target fields. Thus, manual identification of candidate field, e.g., [REC_COSTS] is required.
2. Another example is due to plural/singular words. A legacy fieldname including the word "*Controls*" is not found since a potential target field candidate may only contain its singular reference such as "*CONTROL*"

Neither of the 2 scenarios should pose a concern though. That is, the amount of fields not being matched is relatively short (about 15% for this data set).

Once again, I'm thrilled about this fantastic tool!!

Cheers,
Tom

[skydivetom]

Vlad -- instead of using a toggle button, I added a list box (top right in header) which allows to do the following:

1. Open query where no target fields were matched at all. --> This results in POC having to review keywords/add new keywords.
2. Open query where target fields have been matched up but none have been selected yet. --> Review/selection of target fields is needed.

What do you think about this alternative?

Cheers,
Tom

[skydivetom]

I just posted the latest version... (post #38). Again, I'm loving it!!

Just noticed one small hiccup though wrt to the 3 cascading combos. If user select a value from the 1st combo but leaves 2nd combo (and 3rd combo) empty BUT clicks on "Find Record" an error is thrown. See attached JPG.

I'm gonna check out if I can throw a custom message box instead indicating that 2nd combo/3rd combo must be selected before click on command button. I'm sure there's an easy solution for it.


UPDATE TO POST #39:

I added the following code. User can't click on the command button until value in combo #3 has been selected.

Private Sub Form_Open(Cancel As Integer)


    ... some code
    
    'Disable command button
    Me.cmdFindRecord.Enabled = False


    
End Sub

Private Sub cboFIELD_ELEMENT_AfterUpdate()


    Call cmdFindRecord_Click
    
    'Disable command button                      'New line
    Me.cmdFindRecord.Enabled = True


End Sub