Compile Data and Produce a Dashboard

[Mahendra S]

 

[Mahendra S]

One more observation, NewDraft_July25-L2 is working fine, but when I compile vba project it was throwing diff err like below

 

[Luke M]

Hmm. Looks like some extra references got added into the VB. Not sure how that happened, but they don't need to be in there, as I was making sure to use late binding rather than early binding. I've cleared out references in the attached, removed bad form call from wsAdmin that you found via Compile.

 

[Mahendra S]

Thanks for the update Luke, quick validation seems passed all the errors,
does references show any missing, please let me know.
I hope we have all variables properly declared across.

Will further evaluate on Save/Replace WS Table, probably we can make it simpler to avoid multiple messages.

Regards,
M

 

[Luke M]

No more missing references that I can see. Also did a compile check, everything looked good there. I use Option Explicit at top of modules, so no variables can be used without being declared. The usrFrmMultiImport was skipped because it wasn't being treated as a variable, but as an object. Plus, that macro wasn't getting called (looks like a leftover from some earlier development work). Did a look through of all the macros on wsAdmin, and they seem to be good.

 

[Mahendra S]

Thanks Luke, Option Explicit is the good one to keep track unknown variables to find out..
Will get back on Save/Replace WS Table functionality, need to have good interface with some names rather than numbers 1, 2,3 etc.

Also is it restricted to only 5 saves or is it n number of saves, please let me know.

Regards,
M

 

[Luke M]

N number of saves. Used numbers currently just so that the macro can auto-name the different scenarios (Max number + 1), but could change to text if needed.

 

[Mahendra S]

Good to know about multi number support for Save/Replace WS Table, will get back later on this.
Thank you very much for great support Luke!

 

[Mahendra S]

Hi Luke, the Save/Replace WS Table functionality is great, need few tweaks on this as below.

Enhancement1: Instead of saving the sa_sheets table by clicking the button 'Save/Replace WS Table' can we just save as soon as publish operation completes and automatically save it to wsRecords.

This way we can avoid asking for Save every time. I would like to have some intelligence here, is it possible to verify if the sa_table set already exists or not in wsRecords.

If sa_table recordset exists then simply ignore.
If sa_table recordset do not exists then add/ append it with new record number.

Keeping track of unique sa_table set might be bit challenging but need your thoughts on this.
if this enhancement is feasible, we might take out Save from button and avoid save Table pop up.


Enhancement2: Is it possible to pop up (like usrFrmImport or Export, a similar screen with list box values for sa_table structures nnstead of numbers 1, 2, 3 can we have

SA_Publish_Standard FootPrint1, SA_Publish_Standard FootPrint2..SA_Publish_Standard FootPrint'n'
and these names need to be displayed in list box with popup form.

No changes in the draft template.

Thank you,

Regards,
M

 

[Luke M]

Are you sure you want to save after every publish? That sounds like it would create a lot of table records. Trying to match a specific layout to see if it's already existing gets tricky...would need to also check in case the worksheets are listed in a different order presumarbly. If we do figure out how to autosave it, can I presume that they all get named "SA_Publish_Standard FootPrintX" where X is some number? If that's true, I'm not sure why displaying the names in a ListBox is important/good, as they are just become numbers...not really an improvement over current selection method.

 

[Mahendra S]

Thanks for verification Luke, I agree, the save after publish is quite tricky as well as it opens the doors for other permutations and combinations.
Currently duplicates tables are getting saved in to wsRecords which is not a good solution ether, can we avoid this not sure how to find unique trace every time you hit Save it should recognize whether the sa_table exists or not

If sa_table recordset exists then simply ignore.
If sa_table recordset do not exists then add/ append it with new record number

Let us leave as-is for now how ever I certainly look for dropdown(instead of list box) to have SA_Publish_Standard FootPrintX to choose with one for publish, this will have better control and later we can make some other meaning ful appendix instead of X (which is number for now), please let me know what you think.

 

[Luke M]

How many different table sets do you expect to deal with? You had initially said 5. I was thinking that we quickly setup the 5 tables, and then don't really need to worry about the save feature. As I tried to allude to, I'm not sure how to completely check if a table layout exists already, as we'd also have to worry about the order of items moving around.

The choice of using a input box, dropdown, or listbox somwhat depends on how many entries you have. Dropdown is nice with only a few, but gets tiresome to scroll through if you have a lot. Listbox is slightly better with longer entires. InputBox gives fastest response, but you have to know what to type (hence why I only used numbers to start with).

 

[Mahendra S]

Yes, let us have 10 tables just in case safe side, agree to maintain the unique combination table structure is challenging in this scenario.

I would use a dropdown as the reason behind is as-is we only run publish based on only one sa_table set defined from default macro/edit the table or call the SA_Publish_Standard FootPrint'x'
also do not want to enter any unknown text or numbers.

We might change to list box in the future if we have multi sa_publish tables capability, but currently we are good with one sa_table publish.

 

[Mahendra S]

Hi Luke, not sure if you are changing anything with SA_Publish_Standard FootPrint'x'

also another suggestion is can we have SA_Publish_Standard Footprint1-ddmmyyyy in this format and this name should be stored in wsRecords in new column. Please let me know your thoughts.

I still look forward for dropdown to have above names.

Regards,
M

 

[Luke M]

Hi M

It's a bit rough on apperance, but I got a UniqueRecords function built, as well as the list box form setup. Let me know what you think.

PS. Ugh, just saw I wrote this post last night, but didn't publish it. Doh!

 

[Luke M]

For the naming, not sure completely how convention works. If I do 2 saves today, would they be:
SA_Publish_Standard Footprint1-31072014
SA_Publish_Standard Footprint2-31072014

Or something different?
THen, if I do one tomorrow, and 1 day after that, it would be this?
SA_Publish_Standard Footprint1-01082014
SA_Publish_Standard Footprint1-02082014

 

[Mahendra S]

sorry one correction Luke, the format should be mmddyyyy

SA_Publish_Standard Footprint1-07312014
SA_Publish_Standard Footprint2-07312014

and next day if another one it should be
SA_Publish_Standard Footprint1-08012014

 

[Mahendra S]

the draft looks good and better if we can make it with mmddyyyy format and also need to have cancel along with preview.

 

[Luke M]

Name format has been setup, and date format is mmddyyyy.

 

[Mahendra S]

Thanks Luke, will verify and get back later.

 

[Mahendra S]

Hi Luke, The RecExists Function is a great implementation, just to be on same page are you restricting this function for only 10 saves or will it be 'n' saves based on Publish, looks like this function has intelligence capability and do not restrict with 10 saves, how ever please confirm on this.

I did 4 validations so far looking good, will have to do through validation on this.

(When you have like many many multi tabs the standard foot print will help if these tabs remain same to avoid manual input with sa_table edit after import)

clarification1: UniqueRecords function will have any performance impact if you have 30 or 40 tabs just as an example hopefully no. any way I can setup some data for this later to evaluate, let me know your thoughts.
I hope this functionality is expected as a solid and fool proof one, which brings the state of the art value to the template!

I have following enhancement for Search functionality.

Enhancement1: as-is when user enters ticket/defect# the found or no found record is shown which is correct.
how ever when we find record in the same Search screen need to display following ticket/defect related information, probably we might move the text box little up parallel to ok button and in the space below the additional details to be shown if record is available.
Ticket/Defect#: <value>
WS Source: <Value>
Id: <Value>

If record is not available asking to search in un published tabs, is it possible to have this message in same search instead in another pop up please let me know.

However if not possible to have above message in same Search screen then we can have as-is in Not Published popup, however if a record is found in un published tab then above three fields need to be shown in Search window itself.

Enhancement2:
In SA tab can we have Group names in alphabatical order starts with A to Z
as -is we have as below



instead we need to sort A to Z (alphabetical, in above example need to start with ATR and so on..) and display dynamically after Publish completes.


Please let me know your thoughts on above enhancements..

attaching the new draft, just adjusted the buttons on Change WS Publish popup.

Regards,
M

 

[Luke M]

RecExists is flexible to handle N records.

Re: Clarification1
Obviously it will take some amount of processing time longer to compare a large table, but I don't think it will be noticeable to user. What takes a long time (in terms of processing) is reading/writing to the Excel file to VB. No matter size of table, we read the entire table in one shot into a variable, and then work with the variable in VB. With that, should be able to process up to 1000 rows (sheets) in < 1 sec. Should be "good enough".

Re: Enchancement1
If record has been published, search result msg box has been updated.
I'm not completely sure what you mean by having the "search unpublished" in same serach...I asummed you meant tojust go ahead and search unpublished records w/o prompting user. If unpublished, msgbox appears with slight differences:
Exclamation icon added to display, help user realize that it is unpublished
WS name given instead of WS source (as WS Source has not been determined due to it not being published yet)
MsgBox title change slightly

Re: Enchancement2
Sort line added to GroupSync macro. As Publish macro uses the GroupSync, this was the best place to put the sort.

 

[Mahendra S]

Thanks for the clarification Luke!

Regarding the Search, the functionality works with ticket details now.

Earlier I was thinking to show the search results in the first window (Search window ) itself, rather displaying in 'Published Record Found' pop up (second window).

basically just to avoid too many popups where ever possible, please let me know if we can avoid second pop up.
(like example, we had everything in Sign On window for username validation for SA tab)

Regards,
M

 

[Luke M]

Ah, I see. In that case, rather than a sub, I'll change it to a function, just like we use in an XL formula. In latest attached, built a small user form which has a single input field and the output caption is result of formula.
Or, we can skip the button/form all-together. If you look at right side of SA, I put a entry cell w/ output formula. We can go with either one, just delete the one you don't like.

 

[Mahendra S]

Thanks Luke, few observations...

The new dynamic search screen is good, after search the first statement should be underline (Published Record or Un Published record) probably with red font and size10.

remaining details in black font with 10.

However encountered an err when attempted with Publish with latest, some where we are missing err handle
Click Publish with what you have in table, giving below



click ok, throws run time err as below.


Need to investigate where the Publish is failing now.

Regards,
M