exported grid to excel shows fields in repeat value = ON

Scriptcase 9 Bugs
#1

[B]============= CONTACT DETAILS =============
sawjer
5 - Version: 9.3.6
============= PROBLEM DATA =============
5 - Description of the problem *: The fields of a grid with setting “repeat value”=off show correctly and exporting the grid shows incorrectly (repeating above values)

6 - Step by step problem *:
There are tow tables:

  • bugtest_data (table of grid)
  • bugtest_data2 (linked to bugtest_data in event->onRecord:
    sc_lookup(rs,“SELECT addon_text FROM bugtest_data2 WHERE bugtest_data2_id=”.{bugdata_id});
    if(!empty({rs})){
    {addon}={rs[0][0]};
    }[/B]
  • bugtest_data2 has only two entries, addon_text1 and addon_text3

The grid in the browser shows as expected, The export of this grid repeats the addon_text1, which is wrong. The same can be seen in pdf and word export.

This is a crucial problem in the project, as the customer needs the ouput and will refuse acceptance of the delivered software. Thank you for help

bugreport.png

#2

Did you try creating a SQL select that joins the tables, so that “addon” is generated by the application SQL statement and not defined\populated elsewhere inside the app?

Judging by how “Bug Value” is rendered, “standard” fields seems to be rendered in the same ways on the app and in the excel.
(I’m assuming bugdata id 2 has a Bug Value of Enitrag 1 that is not shown because you set that field to not repeat values).

If that doesn’t help, I think you need a virtual field and some PHP code: on each record you check the value of “addon” in the previous record; only if it’s changed compared to the value of “addon” in the current record you populate the virtual field with the current value, if not you set the virtual field to blank.
Hoping that virtual fields are rendered correctly in the excel export…

#3
  • There is [SIZE=18px]no[/SIZE] recoird with bugdata id 2, value = “addon_text3”.
  • I already use a virtual field to display the values from table bugtest_data2.

i have no way to influence the creation of excel in this respect. Perhaps there are unknown ways to solve this. I welcome further tips for this critical problem.

#4

Well, still the suggestion of programatically populating the virtual field only when changed compared to the previous recor should work, as the virtual field will have a value only when you want to.
So there shoud be no risk of the excel export showing “duplicate” values that are hidden in the grid.

#5

same problem and i have gold support this is the response:

#########################################

Hello Sir,

I’m sorry for the delay and for all these problems that you faced. This problem has been handled by another team to look for this possible bug.

But, this is not a Scriptcase bug. When exportation occurs to Excel, the Scriptcase needs to fill the cell, because that’s the excel behavior. And in this case, the Scriptcase fill these repeated values with zeros.

If you need assistance with another matter, we’ll be glad to help you.

Best regards,

Matheus Nic?as
Scriptcase Support

[SIZE=14px]this is the support response after 21 days![/SIZE]

#6

You have to add a value to {addon} when it could be empty. So change your code to

if(!empty({rs})){
    {addon}={rs[0][0]};
}else{
    {addon}='';}

Worked?

#7

@nonkelmike

the issue is that that field has a value, but he doesn’t want to see it when it has the same value as the previous record.
the SC setting to get that behavior seems to work only on the html rendering of the grid; the excel export ignores that setting.

@sawjer

it’s a workaround, but it should be easy to do what I described.
Something like this:

OnScriptInit

[prev_val] = '';

OnRecord


$curr_val = '';
sc_lookup(rs,"SELECT addon_text FROM bugtest_data2 WHERE bugtest_data2_id=".{bugdata_id});
if(!empty({rs}))
{
    $curr_val = {rs[0][0]};
    if (empty($curr_val))
    {
        $curr_val = '';
    }
    if ($curr_val <> [prev_val])
    {
        {addon} = $curr_val;
    }
    else
    {
        {addon} = '';
    }
}
[prev_val] = $curr_val;

I didn’t write est the code in SC so you should check for syntax errors or maybe even something set or checked at the wrong time or for the wrong value.
Also I guess it could be simplified\optimized.

But anyway I’m pretty sure that it can be done, assuming whatever is programatically coded as output for a virtual field is rendered the same in the HTML output and in the excel export.

#8

Today, 04:16 PM
Thank You all for the effort and time invested to help me.

I have tried to put the suggestions into code and this is the result:

  1. I have removed as may virtual fields as possible. Except for virtual fields browser and excel display are the same.

  2. The virtual fields are age values, calculated to the current date. The fields have repeat value=OFF.
    In the browser alle age fields show as expected ( following the birthday field from which it is derived)
    In der exported Excel age fields are shown as expected, but then they are repeated up to the next, different value.
    Those repeated fields are empty fields in the browser, (which can be verified, because there is no birthday field for the age value!

My conclusion:

  • The error in the Excel (and also in pdf and word) only happens with virtual fields.

  • I seems to me, that for the virutal fields the repeat switch seems to be on.

  • What is the possible work around? With programming nothing can be done, because there is no data at that position.
    The only possible solution is to create a table with all fields needed in the output, truncate the table and fill it at onApplicationInit. This has to be repeated for evey application that needs to esport (some 30 apps).

I ask the scriptcase support team to look into the matter. My project is stuck nearly at the end of development. Thank you for help!

#9

hi,
I have the same problem, when I export a grid with virtual fields, some are exposed, others are not, yet the calculation is identical. I wrote to the support about a month ago and I haven’t had a solution yet.