Dynamic columns in grid application

Scriptcase 8 Discussion
#1

Hello guys,

I am trying to build grid application based on a dynamic structure generated by another query. Let me show you.

  1. I have an evaluation format that has a min and max value, for example:

select min(required), max(required) from evaluation where day = today

Everyday these values changes, for example, today could be [1, 5]; tomorrow could be [2, 7]

  1. I need yo build a grid where I need to show the grades based in the evaluation format. For example, for today [1, 5] the grid should be displayed like:

// this query will return: frank, 4
select name, grade from grades where student = x;

Name | 1 | 2 | 3 | 4 | 5

Frank |…|…|…| x |…

What does happen in the next day for evaluation format [2, 7] with the same student? This:

Name | 2 | 3 | 4 | 5 | 6 | 7

Frank |…|…| x |…|…|…

My problem resides in how I build the grid’s columns based in the result of another query. The rest is another story. Anyone has an idea? I have a “my last card” idea using an UNION statement. but may you have a coolest idea. Thank you in advance.

#2

You cannot have grid with dynamic columns. The only solution - and I don’t know if that fits your need - is to create a full grid with all columns and hide columns depeding on the situation. The point is that SC is depending on the data structure. The query is fixed and used to generate the column headers.

#3

I ended doing this manually querying the data in an event and building a table there. Thank you so much.

#4

Hi Manfred, Care to share exactly how you did this ? I posted a question about this after replying to this post and it was deleted. It is a limitation of SC 8 that you cannot have dynamic forms or grids, you have to go back to the grid or form application and either edit the sql (Grid) or synchronize(form). This means that any requirement to add columns dynamically eg user has the ability to add fields, is not catered for natively.

#5

There is a way but its WAY more difficult to do than necessary if SC just allowed a variable in the SC_Macro.

What I did as a workaround is create 10 grid fields labelled 1-10, 10 being the maximum columns I can possibly have.

I then do a SQL lookup on the onApplicationInit event to give me a comma seperated list of the items I would like to use as column names.

I also do a count of the number of results of the SQL lookup.

You cannot run the column name update (sc_label({8}) = ‘something’) or the hide column (sc_field_display(8, off) functions in any loop or while function.

I therefore first wrote 10 if statements based on the count of the items to dynamically hide excess columns with a if function. If there are 3 items, the IF statement is hard-coded to hide columns 4-10.

Then I use explode function to get each column name into an array and then call the array value again in 10 seperate if statements to give each column its unique name.

Again, lots of work and not practical if you have an almost endless list of possibilities but worked in my scenario.

Sorry the code is messy and not cleaned up but if you searched as long as I did, it may be worth trying to follow to find a solution.

$updateloop = "SELECT * FROM B_Lease";
sc_select(listofleases, $updateloop);
$numberoflease = {listofleases}->_numOfRows;
//echo $numberofcosts;
sc_lookup(listofleases2, $updateloop);
$key = ‘’;
$costs = ‘’;

$countofleases = ‘0’;

while($countofleases != $numberoflease){

	$ID = {listofleases2[$countofleases][0]};
	//echo $ID;
	//echo '<br>';


$key = $key.','.$ID;

	
$countofleases = $countofleases + 1;
}


$key = substr($key, 1);

//echo $key;
//echo ‘
’;
$costloop = “SELECT DISTINCT (Costtype) FROM B_Lease_Cost WHERE FIND_IN_SET(Linkfield, ‘$key’)”;
sc_select(listofcosts, $costloop);

$numberofcosts = {listofcosts}->_numOfRows;
[nocosts] = $numberofcosts;
//echo $numberofcosts;
//echo ‘
’;

sc_lookup(listofcosts2, $costloop);

$costcount = ‘0’;
while ($costcount != $numberofcosts){

$Costname = {listofcosts2[$costcount][0]};

$costs = $costs.','.$Costname;

sc_label({1}) = '$costs';


$costcount = $costcount +1;
}

$costs = substr($costs, 1);

[listofcosts] = $costs;
echo $costs;
echo ‘
’;

//sc_field_display({1}, on);
//sc_label({1}) = ‘Turnover Rent %’;

$numberofcosts = [nocosts];
$numberofcostsdelete = $numberofcosts + 1;
echo $numberofcostsdelete;

while ($numberofcostsdelete < 11){

	echo '<br>';

echo $numberofcostsdelete;
echo '<br>';





$numberofcostsdelete = $numberofcostsdelete+1;
}

if ($numberofcosts == 0){
sc_field_display(1, off)
sc_field_display(2, off)
sc_field_display(3, off)
sc_field_display(4, off)
sc_field_display(5, off)
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 1){
sc_field_display(2, off)
sc_field_display(3, off)
sc_field_display(4, off)
sc_field_display(5, off)
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 2){
sc_field_display(3, off)
sc_field_display(4, off)
sc_field_display(5, off)
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 3){
sc_field_display(4, off)
sc_field_display(5, off)
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 4){
sc_field_display(5, off)
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 5){
sc_field_display(6, off)
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 6){
sc_field_display(7, off)
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts == 7){
sc_field_display(8, off)
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts ==8){
sc_field_display(9, off)
sc_field_display(10, off)
}

if ($numberofcosts ==9){
sc_field_display(10, off)
}

$costsarray = [listofcosts];

$arr = explode(’,’, $costsarray);
$arraycount = count($arr);

//$count = 0;
//$colcount = 1;
//while ($count != $arraycount){

//sc_label({"$colcount"}) = $arr[$count];

//$colcount = $colcount+1;
// $count = $count+1;
// }

if ($arraycount == 10){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
sc_label({6}) = $arr[5];
sc_label({7}) = $arr[6];
sc_label({8}) = $arr[7];
sc_label({9}) = $arr[8];
sc_label({10}) = $arr[9];
}

if ($arraycount == 9){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
sc_label({6}) = $arr[5];
sc_label({7}) = $arr[6];
sc_label({8}) = $arr[7];
sc_label({9}) = $arr[8];
}

if ($arraycount == 8){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
sc_label({6}) = $arr[5];
sc_label({7}) = $arr[6];
sc_label({8}) = $arr[7];
}

if ($arraycount == 7){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
sc_label({6}) = $arr[5];
sc_label({7}) = $arr[6];
}

if ($arraycount == 6){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
sc_label({6}) = $arr[5];
}

if ($arraycount == 5){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
sc_label({5}) = $arr[4];
}

if ($arraycount == 4){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
sc_label({4}) = $arr[3];
}

if ($arraycount == 3){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
sc_label({3}) = $arr[2];
}

if ($arraycount ==2){
sc_label({1}) = $arr[0];
sc_label({2}) = $arr[1];
}

if ($arraycount ==1){
sc_label({1}) = $arr[0];
}
//Bring in number of costs
//Loop from 1-number , lookup in array of cost names and rename col name
//number of cost + 1 loop to x and hide each col.

1 Like