Create a New Ad-Hoc by Copy/Pasting Special an Existing Ad-Hoc

Charlie_Geddes

Hi Everyone, 

I would like to bring attention to ER95605. I initially created this ER in January 2023, it got three endorsements, but then it was archived by Infor. Infor suggested I re-submit the ER, which I just did, and now I can suddenly see ER95605 again. I am not sure how/why it reverted to my original ER, and I can't actually see it in the Enhancement Requests page of Infor Concierge, so I'm not sure if you will be able to. So, I'll just have to describe the ER in this post. 

Today - users who have built an ad-hoc report in the Excel add-in can copy/paste special the report, and it creates a second ad-hoc report. The problem is that the two reports have the same filters, column selections, and row selections, so any changes made to one report affects the other report too. If you have never used an ad-hoc in the Excel add-in, it is a great way to pull and research data, and an ad-hoc functions the same way a pivot table does. Your average finance and accounting person in this day and age is comfortable in Microsoft Excel and has probably built their fair share of pivot tables, so ad-hoc reports are a useful and familiar way to self-serve. In Excel, it is quite easy to create a new and independent pivot table from an existing one. You can 1) create a copy of a tab, 2) select the whole pivot table, and copy/paste the pivot table, or 3) save as the full workbook under a new name. These are three ways to creates a new pivot table with independent selections, and it saves users time from having to build a pivot table from scratch. The ad-hoc feature in the Excel add-in does not function like this, and I think only the 3rd option I listed previously works to create totally new and independent ad-hoc report. 

Users regularly ask how they can quickly create a new ad-hoc. I have become very familiar with the tool, and I can create a new ad-hoc in about a minute, so it is not disruptive for me. New users on the other hand might spend 10 minutes setting up an ad-hoc and validating the data, but then there is no quick or easy way to create a copy of the ad-hoc. This user would then have to 'recreate the wheel', and that may be a frustrating experience! I believe the functionality that I have described would be a great UX enhancement to the add-in, and I've probably had 5-10 people ask for it over the past two years. 

I will reply once I sort out if/how this ER can be endorsed, but I wanted to explain the issue and seek a verbal show of support. Thanks!

Vit Holy

Hello Charlie,

I've used chat gpt and prepared quick javascript code to do the cloning of the ad-hoc. You can use it through office scripts (javascript based automation). Hope it is enabled on your end by IT. If so, you can go to "Automate" tab and add new script and that can be later used or even added to the workbook as a button. 

It is very simple script which works the way that you need to select/activate the spill parent cell (i.e. cell containing the formula) of the ad-hoc area and when run it will:

1. Copy the ad-hoc definition to another cell
2. Copy ad-hoc formula below current ad-hoc and reference the new definition

This way those two 'ad-hocs' will be disconnected/independent and so you can just modify the second one without changing the first one.

javascript:

function main(workbook: ExcelScript.Workbook) {

    let activeCell = workbook.getActiveCell();

    let formula = activeCell.getFormula();

    if (!formula.startsWith("=")) {

        console.log("Active cell does not contain a formula.");

        return;

    }

    let spillRange: ExcelScript.Range;

    try {

        spillRange = activeCell.getSpillingToRange();

    } catch (e) {

        spillRange = activeCell;

    }

    let initialSpillRow = spillRange.getRowIndex();  // 0-based index

    let initialSpillRows = spillRange.getRowCount();

    console.log("Row/Rows: " + initialSpillRow + " / " + initialSpillRows);

    const searchStr = "'Infor-dEPM-Adhoc-Definitions'!";

    let pos = formula.indexOf(searchStr);

    if (pos === -1) {

        console.log("The formula does not contain a reference to 'Ad-hoc definition'.");

        return;

    }

    let startPos = pos + searchStr.length;

    let allowedChars = "$ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";

    let endPos = startPos;

    while (endPos < formula.length && allowedChars.indexOf(formula.charAt(endPos).toUpperCase()) !== -1) {

        endPos++;

    }

    let refCellAddress = formula.substring(startPos, endPos);

    let oldRef = searchStr + refCellAddress;

    let targetSheet = workbook.getWorksheet("Infor-dEPM-Adhoc-Definitions");

    if (!targetSheet) {

        console.log("Sheet with Ad-hoc definitions not found.");

        return;

    }

 

    let colARange = targetSheet.getRange("A:A");

    let usedRange = colARange.getUsedRange();

    let newRowNumber: number;

    if (usedRange) {

        newRowNumber = usedRange.getLastRow().getRowIndex() + 2;

    } else {

        newRowNumber = 1;

    }

    

    let refCell = targetSheet.getRange(refCellAddress);

    targetSheet.getRange("A" + newRowNumber.toString()).setValue(refCell.getValue());

    let a1Cell = targetSheet.getRange("A1");

    let a1Value = a1Cell.getValue();

    if (typeof a1Value === "number") {

        a1Cell.setValue(a1Value + 1);

    } else {

        let num = parseFloat(a1Value as string);

        if (!isNaN(num)) {

            a1Cell.setValue(num + 1);

        }

    }

   

    let newRef = searchStr + "$A$" + newRowNumber.toString();

    let newFormula = formula.replace(oldRef, newRef);

    let lastSpillRowNumber = initialSpillRow + initialSpillRows;

    let newFormulaRowNumber = lastSpillRowNumber + 2;

    let activeSheet = workbook.getActiveWorksheet();

    activeSheet.getRange("" + (lastSpillRowNumber + 1).toString() + ":" + (lastSpillRowNumber + initialSpillRows + 1).toString()).insert(ExcelScript.InsertShiftDirection.down);

    activeSheet.getCell(newFormulaRowNumber - 1, activeCell.getColumnIndex()).setFormula(newFormula);

    console.log("Done!");

    console.log("Copied value from " + refCellAddress + " to A" + newRowNumber + " in '" + targetSheet.getName() + "'.");

    console.log("Incremented A1 to " + targetSheet.getRange("A1").getValue() + ".");

    console.log("Inserted new formula at row " + newFormulaRowNumber + " with updated reference.");

}

----

Disclaimer (as usual):

This script is provided "as is" without any express or implied warranty. The author accepts no responsibility for any damage, loss, or other issues arising from its use. Use at your own risk.