Copy-paste from temporary record to excel

jorgito

Hi all.

I have this strange problem.

I have created a form that displays the Bank Account Ledger Entries. The difference with the standard form is that it groups entries with the same Document No. into one.

It uses a temporary Bank Account Ledger Entry table. When the form opens, it read the entries from the table and copies them to the temporary table. If a Document No. exists already in the temporary table, it adds the amount to the already inserted entry, otherwise, it inserts the entry into the temporary table.

When I open the form, it displays everything correctly.

However, when I copy-paste a row to Excel, I get the amount of the first line of the group.

For example, if I have 4 entries with Document No. 'PMN.00001' with amounts 10, 20, 30 and 40 euros, the temporary table displays 1 entry with 100 euros (correct!). If I copy-paste this row to Excel, it displays 10 euros (only the first entry!!!).

The same happens if I paste into Notepad.

Any ideas?

Thanx
Jorgito

jorgito

It seems that whenever I select individual lines and copy/paste them to Excel, the problem I have described occurs.

But if I select ALL the records using the box on the top left side of the table and copy/paste all the records to Excel, the amounts are OK.

Let me point out that the function FillTempTable that populates the temporary table is called in OnOpenForm only.

Any ideas?
Jorgito

Arhontis

Hi Jorgito (George I assume)

Are you building up a temp rec on the OnOpenForm and then assign it to Rec (standard way)?

In that case the copy paste might use the Rec variable to read from the database and ignore the earlier assign you have made... Really strange indeed... Never heard of it before...

I would expect it if you had a variable on your form that keeps the added amount, and not the table field, but it is not this case... Are you sure you use a table field to accumulate the amounts?

Anyway, one way to skip it, is to make a button that uses the Excel buffer functionality to export you data (by using the temp record) and make sure that you will always have the correct values.

jorgito

EXCELLENT IDEA!!

Instead of using the actual field from the table, I used a variable where I calculate the Amount. In this way, when I copy/paste to Excel, the correct value is pasted.

However, the question remains: Why does Navision copy the value from the original table (based on the Entry No. ???) and not from the temporary table. And why if I select all the records using the top left box, the correct values are copied and not if I select specific rows?

Thanks for the idea, Arhontis...

BTW, I'm using the standard way of displaying a temporary record.

Arhontis

Hi jorgito,

I could not know the origin of the problem, it might depends on the version, what version do you use? Have you tried it on sp3?

Anyway, in case you get it on the sp3 client, then you should post an incident on mbs partnersource to get their opinion.

Keep in mind that using the variable instead of the field, you might get some weird results on the display of the table box (shows values from other recs) when you calculate it in the OnAfterGetRecord.

:-k :idea:
Why don't you make a calculated field to sum the amounts of the same Document No. in Bank Account Ledger Entry so that you get it on the actual table without using temp and get the copy paste correct all the times?
You must keep the code that finds and displays only the unique document no. by using the mark and markedonly functionality on the OnOpenForm...

Haven't tried it but it might end all your problems...

todro

jorgito wrote:

BTW, I'm using the standard way of displaying a temporary record.

did you assign the temporary record to the "SourceTable" property or maintain the form triggers for finding the records in the code ? If not, this might be the problem.

jorgito

You cannot use a temporary record as the SourceTable of a form. You must use a normal record (e.g. Bank Account Ledger Entry) and in the OnFindRecord and OnNextRecord insert code to display the temporary record. That's what I did.

What do you suggest todro?

Arhontis

Arhontis wrote:

:-k :idea:
Why don't you make a calculated field to sum the amounts of the same Document No. in Bank Account Ledger Entry so that you get it on the actual table without using temp and get the copy paste correct all the times?
You must keep the code that finds and displays only the unique document no. by using the mark and markedonly functionality on the OnOpenForm (by marking the actual rec without using a temp variable)...

Tried that Jorgito?

todro

jorgito wrote:

You cannot use a temporary record as the SourceTable of a form. You must use a normal record (e.g. Bank Account Ledger Entry) and in the OnFindRecord and OnNextRecord insert code to display the temporary record. That's what I did.

What do you suggest todro?

sorry, I was not clear enough.

The reason why selecting the upper left corner works is, because Navision is not running into the triggers and simply copies what is "seen". From my experiences, Navision always uses the table assigned by the SourceTable property, even if you override in OnFind/OnNext etc. This is e.g. why Navision gets confused when you add a second tab control and doesn't know which table to use when you hit copy.

Without knowing the form and code, it's hard to suggest a solution but my feeling is, that there is no simple solution unless you are using a "normal" table as temporary storage place and as the tablesource for the form.

jorgito

I can't use a calculated field as the sum I am interested in is more complicated than just adding up the lines with the same Document No. Sometimes, entries are connected to other entries through another field and these entries must be added up to the first ones.

I finally did the following. In OnOpenForm, I fill the temporary record with all the unique lines without calculating the amounts. I read through the actual table and for each line I decide if I should copy it to the temp or not. Then, in OnAfterGetRecord, I calculate the amounts (Debit, Credit, LCY) for each line through its Entry No and store them in variables. I then display the variables instead of the table fields. The result is correct and the amounts are copied correctly.

So you can consider my problem solved.

However, it still puzzles me why even though all the amounts are displayed correctly in the form, they are not copied correctly to external applications. And this happens only if I select individual lines. If I select all the lines using the top left box, everything is copied OK.

Navision can get quite strange sometimes...

Anyway, thanx everyone for your help and your ideas.
Jorgito