Complicated Selection, is it possible?

[richnorris]

I have a request from the business to implement two buttons, which will essentially act as bookmarks. For whatever reason, they are not happy to use bookmarks, so I have to solve it some other way. The two buttons should do the following:

The first button takes a group of 'Sales Clients' (as specified in a spreadsheet) and removes them from the data set, but only where the 'Country' is France or Germany.

The second button is to remove all but one (CEX) 'Extract Codes' when the 'Year' is 2011 and the 'Country' is France or Germany.

It is ok if this is all in one button, rather than in two.

The thing that makes this so difficult, is that it is not possible to create a flag for the second button in the backend. In the frontend, we combine trade data with salesperson data, so we dont actually have the 'Country' until the dashboard frontend is loaded. Joining the employee data and the trade data together, then reloading it all and adding a flag is not plausible, as the trade data must load as an optimised load, or else it takes far far far too long. I do have a flag that is 0 if the 'Year' is 2011 and the 'Extract Code' is not CEX, and 1 otherwise.

What I have tried doing, is to create a button with the following actions:

Select (Germany|France) in 'Country'

Select all the Sales Clients supplied in the spreadsheet

Select all possible values in Row ID (which is the primary key of the trades)

Set a variable (vB2B) to =GetFieldSelections([Row ID],'|',100000000)

Clear all fields

this part is supposed to get all the Row ID s that I dont want from the sales client restriction and save them to a variable, then:

Select (Germany|France) in 'Country'

Select 2011 in 'Year'

Select 0 in the Flag

Select all possible values in Row ID

Set another Variable (vCEX) to =GetFieldSelections([Row ID],'|',100000000)

Clear all fields

this part is supposed to get all the Row IDs that I dont want from the CEX restriction and save them to another variable.

The idea would be to then combine the two strings of Row IDs into a new variable (seperated with a | ) and use this as a selection string in Row ID. This would give me all of the Row ID's I DONT want to see, and then finally do a Select Excluded to get what I do want.

The trouble is, setting the variables just doesn't seem to have any consistent result, and it seems like rather than setting the variable to become the value and store the string, the variable changes along with the selections in Row ID, so once another selection is made, the string of unwanted id's is lost.

I've tried using =$(GetFieldSelections([Row ID],'|',100000000)) instead, but this doesn't seem to do anything.

It seemed like such a straightforward problem, after all its really just mimicking a bookmark, only it seems whatever I do the two different criteria stomp all over each other and leave me with a mess.

Any Ideas would be much appreciated!

[#2]

Hi Rich:

To make the possible data set in the buttons can be done with advanced set analysis concepts like XOR etc..

But however, in your approach can be assisted without all the actions. Variable one can be substituted with

Concat(Distinct {<Country={'Germany','France'},[Sales Clients]={'$(Concat({1} ExcelSalesClients,','))'}>} [Row ID],'|')

Similarly for the second one.

In the button you can select these values for rowid and toggle selections. The advantage is that we can validate the values for the variables (to meet the consistency) and decrease the load of actions.

I hope this helps you.

Regards,

Kiran Rokkam.

[richnorris]

My ExcelSalesClients are stored in a variable (perhaps innapropriately called Var) which stores them in the format for entry into an action, ie,

(First|Second|Third) etc.

How can I fit this into your script?

I also dont fully understand where that should go, are you saying I should have a variable with that as the definition, and then have a toggle select Action in the button that has the variable as the search criteria?

[#4]

Step1: Create a variable vRowID as Expression1&<delimeter>&Expression2.

Step2: In the button, add action as toggle select for [Row ID] with the new variable.

Now to use the variable for excelsalesclients, you can directly replace concat(excelsalesclients) in the set analysis with the variable. Expression can use variable to evaluate so this wont be a problem.

Overall ensure that set analysis is done with right delimiter as i notice usage of "|" instead of default ','.

Kiran Rokkam.