Save failure for incorrect structured reference

[ammosov]

How to reproduce:

1. Open file with a pre-existing AFE module (the one shown has 42 lines).
2. Test saving module. Save operations performs normally.
3. Make any change in the module (src shows adding a new line #43)
4. Save operation fails.

Reproduced in several files.

[jack-williams]

Would you be able to share the full content of module that is failing to save?

[ammosov]

date_after = DATEVALUE("01/01/2018");

date_before = DATEVALUE("01/11/2022");

round_more = 10000000;

round_less = 1000000000;

premoney_more = 0;

premoney_less = 0;

postmoney_more = 40000000;

postmoney_less = 2000000000;

delta_pct_more = 0.05;

delta_pct_less = 0.3;

to_log = LAMBDA(logbase, datarange,
    IFS(
        logbase = "N",
        LN(datarange),
        logbase = 10,
        LOG10(datarange),
        TRUE,
        "logbase must be N or 10"
    )
);

filters = LAMBDA(
    (MainData[Date] >= date_after) * (MainData[Date] <= date_before) *
        (MainData[Money Raised] >= round_more) * (MainData[Money Raised] <= round_less) *
        (MainData[Post-Money Valuation] >= postmoney_more) *
        (MainData[Post-Money Valuation] <= postmoney_less) *
        (MainData[DeltaPct] >= delta_pct_more) * (MainData[DeltaPct] <= delta_pct_less)
);

get_data = LAMBDA(column_name, FILTER(INDIRECT("MainData[" & column_name & "]"), filters()));

get_bin = LAMBDA(column_name, FILTER(INDIRECT("BinData[" & column_name & "]"), filters()));

Before update, everything worked without error messages.

[jack-williams]

Thanks. And just to check, you have a table with the correct names and columns in the workbook?

[jack-williams]

And are you using english locale with ',' separator, or ';'?

[ammosov]

Absolutely.

[jack-williams]

Thanks for extra information. Having difficulty reproducing the error, but I'll continue to have a look. If it happens again in a different situation feel free to update the thread.

[ammosov]

Thx. I cannot remember if I have any other modules done pre-update but will look up now.

[ammosov]

If it helps: I opened a file that had no AFE module and saw that MODULE tab now begins with comment

My previous modules had no such comment part. Perhaps this may be cause of trouble.

[jack-williams]

That shouldn't make a difference. The comment is added when you open a fresh workbook and there is no existing module file or hidden sheet from the previous version of AFE.

[ammosov]

OK. I went through all my other files and it seems that this is the only ones where I created modules before AFE update. Would be glad if this is caused by some formatting or syntax error, although with some effort I can recreate all custom functions from scratch, I hope.

[ammosov]

I think I found something!

filters = LAMBDA(
    (MainData[Date] >= date_after) * (MainData[Date] <= date_before) *
        (MainData[Money Raised] >= round_more) * (MainData[Money Raised] <= round_less) *
        (MainData[Post-Money Valuation] >= postmoney_more) *
        (MainData[Post-Money Valuation] <= postmoney_less) *
        (MainData[DeltaPct] >= delta_pct_more) * (MainData[DeltaPct] <= delta_pct_less)
);

Save operation resumes normally, and new functions can be added IF this part of code is deleted.

What is wrong about this code is that [Date] table field was renamed after its creation. I will test saving after fixing this error.

UPD. After changing [Date] >> [AnnDate] SAVE resumed normally. So it is safe to report it breaks on illegal reference.

[jack-williams]

Thanks for helping to debug this.

Yes, the Excel API (not AFE) will throw a sync failure if you try to write a table reference that does not match a table in the workbook.

We should give errors (red underlines) if a column does not exist.

A limitation in AFE is that if you rename a column while AFE is closed we will not know to rename the references in the module code.

[ammosov]

Glad I could be of help. Consider this a feature request for more verbose error messages and control for illegal inputs inside AFE at such future time as feasible.

A side thought: time may be good for creating a wiki or FAQ section here at this repo for repeat questions "how to reinstall", "how to save" etc.