Subset linked to list and refresh on action button

[manu0521]

Hi ,

I have a pax sheet where i need to show all the leases which has required correction status.

And once they pick 1 of the leases from drop down , i will populate the current attributes for that lease and then give them the option to correct values and resubmit.

I have an action button called as resubmit and on resubmission I am triggering a ti where i am updating the lease status .

so i have a subset which gives me lease with correction required status .

First question is :

How should i link this subset to the list or combo in pax which shows the list of available leases with correction status , should it be subnm or create a active form with this subset and refer the cells with lease id.

I tried subnm and loads fine .

Secondly I would like to behave this list dynamic , so when there is 3 leases in the list or combo and user selects 1 and submits then the status gets chnaged in ti and updates the subset to 2 values and the combo should populate the next item in subset and if there is no items then show blank .

is this possible of auto refresh of values in the list by updating the subset .I am having issues here where the combo values is not refreshing, is it possible to get the first item on the subset and replace on the combo value , meaning if there were 1,2,3 lease and and if 1 is submitted then TI updates 1 to different status , so the combo in pax sheet is to refer lease 2

Any suggestions is appreciated, if anyone has done similar thing.

Thanks,

[manu0521]

Hi ,

I am still looking for a solution ,no luck with ibm as well .

I have a active from which refreshes my subset and puts value dynamically in cell example from C3 to CX based on the result.

Now i am refreing this sheet cels C3:C100 in my list or combo refrence in excel .

Then there is some data that is showed on the subset value and a action button to submit data .

When user submits I am changing the status and then updating the subset and on return , the reference on the active form chnages but not on my list that points to those cells .

Can you guys think of any other way where the excell sheet refrences will capture the change in data .

Thanks,

[declanr]

This is the sort of task where I often find it easier to bring all of the work inside of TM1.

You can create an additional cube with just 2 dimensions - if multiple users work in the sheet at the same time then the first dimension should be }Clients otherwise a driver dimension with 1 element will do. The measures dimension would contain a string element called "Lease".
Add an attribute called Picklist to the measure dimension.
Have the picklist looking at a subset of the Leases which "require correction."

Now instead of the cell that you currently use for your dropdown for the user; you replace it with something that looks at this picklist cell (if you did use the }Clients dimension then they obviously just look at the cell for their own username.)

Now because that picklist cell would be a DBRW; you don't really want other DBRx formulas using it as a reference. So the other cells you have can be replaced with an active form that uses a dynamic subset that converts what is in the new cube to a lease as a single element:

Code: Select all

StrToMember("[LeaseDimension].[" + [NewCube].( StrToMember("[}Clients].[" + UserName + "]"),[NewMeasureDim].[Lease]) + "]")

Then when your TI runs to change the "status" it could also change what the user has in the cell of the new cube (e.g. the first or last lease in the list of ones you want them to look at.)

[manu0521]

Hi ,

Thanks for the suggestion. I created a cube with 2 dimensions ,one is client and other called as picklistMeasure.

Added a string element called 'Lease' and added an attribute called PickList.

Now I assigned the subset:Lease:Lease-Review subset on the pick list cell corresponding to Lease measure.

Now when I open the cube in architect it shows a pick list with the values on the subset .

Now I use a dbrw in my pax sheet and refer the pick list cell instead of using a list in excel .


so now there are 3 leases in the combo and there are few dbra in the next lines that uses this lease number to get some attribute values.


Question 1: You have given a expression to use to retrieve other values . is that still required if i use DBRA
"Now because that picklist cell would be a DBRW; you don't really want other DBRx formulas using it as a reference. So the other cells you have can be replaced with an active form that uses a dynamic subset that converts what is in the new cube to a lease as a single element:"

StrToMember("[LeaseDimension].[" + [NewCube].( StrToMember("[}Clients].[" + UserName + "]"),[NewMeasureDim].[Lease]) + "]")

What is the above code doing ,is it taking the lease number from picklist cube and converting


Question 2:

I still continued with my DBRA and then I chose a lease , say it was 12 (list had 12,13,14) and then on action button hit submit , it changed the status and updated the subset in ti , but my cell value still had 12 , but on opening the dropdown I saw only 13 and 14 .

Now how do I make it 13 show on the cell when 12 is submitted and if there is nothing show a blank .

"Then when your TI runs to change the "status" it could also change what the user has in the cell of the new cube (e.g. the first or last lease in the list of ones you want them to look at.)"

how to chnage the cell value to 13 .

Thanks,

[manu0521]

Hi ,

Any help on Question 2 will be appreciated .

Stuck at this point not knowing how to reset the cell value for the lease that was submitted .

Thanks,

[declanr]

Question 1:
StrToMember("[LeaseDimension].[" + [NewCube].( StrToMember("[}Clients].[" + UserName + "]"),[NewMeasureDim].[Lease]) + "]")

What is the above code doing ,is it taking the lease number from picklist cube and converting

This code when modified to handle your dimension and cube names would return a single element in a subset that is the same as the one selected in the cube by the user. This is an alternative to the DBRW method you are using; when nesting DBRWs inside each other or referencing one in another it can cause incorrect results as you don' control the order that they are calculated in.

Question 2:

I still continued with my DBRA and then I chose a lease , say it was 12 (list had 12,13,14) and then on action button hit submit , it changed the status and updated the subset in ti , but my cell value still had 12 , but on opening the dropdown I saw only 13 and 14 .

Now how do I make it 13 show on the cell when 12 is submitted and if there is nothing show a blank .

"Then when your TI runs to change the "status" it could also change what the user has in the cell of the new cube (e.g. the first or last lease in the list of ones you want them to look at.)"

how to chnage the cell value to 13 .

You have a TI that you are already executing; so you add additional code into that TI - to change the Cell in the cube where the user can select a lease.
Use a SubsetGetElementName formula to return either the first or last element in the subset. Then use a CellPutS to place that in the cube cell.

[manu0521]

So this what I am understanding .

Choose lease : Cell A4 ,A4 has the list of leases for example 1,2 and 3 and the source for this is a tm1 formula

=DBRW("BoraBora:LeasePickListCube","clientname","Lease Review")

The above stament reads from picklist with a subset linked to it .

Now i pick lease no 1 and the cell A4 has 1 as a value on it

Now I hit the action button and this triggers a TI ,

In the TI code I am removing the value of 1 from the subset and then the code returns back to sheet .

so in the ti once after i update the subset , how will i update the cell A4 in the sheet .

As you stated I can use subsetgetelementname and get the new first element in the ti but then how will I write it back to the cell A4

A4 already has a formula refering to pick cell .

Sorry for my basic questions on this .

Thanks,

[declanr]

So this what I am understanding .

Choose lease : Cell A4 ,A4 has the list of leases for example 1,2 and 3 and the source for this is a tm1 formula

=DBRW("BoraBora:LeasePickListCube","clientname","Lease Review")

The above stament reads from picklist with a subset linked to it .

Now i pick lease no 1 and the cell A4 has 1 as a value on it

Now I hit the action button and this triggers a TI ,

In the TI code I am removing the value of 1 from the subset and then the code returns back to sheet .

so in the ti once after i update the subset , how will i update the cell A4 in the sheet .

As you stated I can use subsetgetelementname and get the new first element in the ti but then how will I write it back to the cell A4

A4 already has a formula refering to pick cell .

Sorry for my basic questions on this .

Thanks,

The sheet is using a DBRW; this is just allowing you to read and write from a cell in TM1 - if the cell in TM1 is changed then the DBRW formula will recognise that. So you can ignore the fact that Excel exists and just focus on changing the value in the TM1 Cube's cell.

So after you have updated the subset in your TI process. Add additional lines (CellPutS as mentioned above) to change the contents of the cell.
Assuming that you are running your TI from an Action Button (or Process Button if we are in PAX) make sure you have the refresh/rebuild option ticked so that when the TI finishes running; your sheet will be refreshed.

[manu0521]

Hi declanr,

I think I finally got it . I am trying to write to the cell using cellputs with say the first value in the subset after refrsh of subsets.

so this works overwritting the cell value in excel as well .

But when i click on the dropdown of excel that value that got deleted from the subset still exists and it goes only after if i select any other item .


The setting on the button has Automatically calculate sheet checked .

I tried the option rebuild sheet as well .

Can you think of any reason why the values in the picklist will not refresh and will only do after i reslect other item from the pick list .
Only after the selection of any items my pick list is showing refreshed data .

so 1,2,3 is there
1 is selected and submitted , 1 is removed in subset and cell in the sheet changes to 2 . now when i open the picklist I still see 1,2 and 3 and then when click on 2 now and then reopen the picklist 1 is gone .

[manu0521]

Hi All,

Afer some suggestions from DECLANR I am close to my solution needed and I am stuck at one last step .

I have a cell that pulls data from a tm1picklist cell using dbrw . By clicking a submit button on pax the subset thats linked to pick list is updated .
Subset changes in ti and then the cell is overwritten with the new first value on subset and data refreshes based on the overwriiten value in the sheet.

The only issue is on going and clicking on the drop down list still shows the deleted subset member in the pick list in pax and it only refreshes until the next selection is made like selecting another item.

Is this how it is intended to work or is it something i am doing wrong .

Thanks,

[declanr]

A cells picklist should be re-evaluated every time you use it; so you shouldn’t see any values that are not in the linked subset or the value already in the cell.

Are you sure there isn’t still an old excel data validation list on the cell or something like that?

[manu0521]

no there is only one formula on the cell.

=DBRW("BoraBora:LeasePickListCube","user","Lease Review")
I checked and there is no refrence data.

IBM says its a defect.But I am seeing if I can get this over by some workaround.

the thing is cellgets chnage is reflected , so 1,2 and3
now 1 is submitted and replaced by 2 ,but then i am wondering if its a bug the pick list still carries the old value .

[declanr]

If you select “1” after it shouldn’t be there do you at least get an error saying it’s not a valid selection?

If IBM mentioned it as a known defect did they give you a reference number for it?

[manu0521]

They are going to log as APAR.

I tried selecting the same number "1" and it resetted to 2 by itself .



Thanks,

[manu0521]

Hi Declanr ,
It works perfectly fine in PAW but not in PAX .

Our plan was to ask users to use from PAW.So we should be good.

Thanks for all your help on this . Really appreciate your time taking to help on this. I woul have been still in step 1 with out your help .