Not a QQ: Need ideas on how to write complete query user interface

Question

5.00/5 (1 vote)

See more:

Hi,

I'll try to be as brief as I can:

The current leg of my project involves taking data from a spreadsheet, making adjustments to the data and then uploading it to a live db. I now have the data and need to work out how to make the adjustments.

The issue is that the adjustments are not set in stone. They can change as often as daily in some cases. My MD will be using the system at first, but we will want to commercialize it along with another large product at some point.

I need a clear mechanism to allow the user to dictate what adjustments need to be made to the data.

Some examples of these adjustments (I'm going to call them Rules) below:

XML format just makes it easier to read here (I hope!), but I'll probably have to store the rulesets like this.

The following rule selects rows where ProdCode begins with "TL" and PClass is either 3 or 4.
It then creates duplicates and sets the qty upper and lower bounds adjusting the rates for each

HTML

<RuleSet supplier="MySup" ValidFrom="01/01/01">
  <Rule>
    <RowSelectors>
    <AND>
      <Selector Column="ProdCode" BeginsWith="TL" />
      <OR>
        <Selector Column="PClass" Equals="3" />
        <Selector Column="PClass" Equals="4" />
      </OR>
    </AND>
    </RowSelectors>
    <RowOperations>
      <Duplicates>
        <Duplicate>
          <Set Column="Upper" Value="1" />
          <Set Column="Upper" Value="1000" />
          <Add Column="DayRate" Value="0.1" >
        </Duplicate>
        <Duplicate>
          <Set Column="Upper" Value="1001" />
          <Set Column="Upper" Value="4000" />
          <Add Column="DayRate" Value="0.2" >
        </Duplicate>
        <Duplicate>
          <Set Column="Upper" Value="4001" />
          <Set Column="Upper" Value="10000" />
          <Add Column="DayRate" Value="0.3" >
        </Duplicate>
      </Duplicates>
    </RowOperations>
  </Rule
</RuleSet>

I kid you not, this is the simplest currently valid rule and there are 27 different "ProdCode starts with"'s with different values

I could get the code side to work from a ruleset like this, but How would I present these options in a UI. Are there examples out there or is there a similar product that I can look to?

Thanks
Andy

Posted 19-Jan-16 3:16am

Andy Lanng

Add a Solution

Comments

Kornfeld Eliyahu Peter 19-Jan-16 9:40am

It can be a very complex UI...
We have a filter window inside our system, that enables to the end user to filter the result set displayed on the current window...
It based on the very same SQL used to fill the window and we display all the fields from that SQL (including anything from the JOINs) to the end user to choose from and build a filter...(we also have extensive metadata to display human readable names for each and every field)...
Visualy, the filter windows has two parts - one for selecting the field and the value to compare to, add OR/AND relations and parenthesis, the other to display the filter in human readable format...

Andy Lanng 19-Jan-16 9:42am

Would it be possible to see a screenshot of the filter UI, please ^_^
or maybe a rough sketch?

Kornfeld Eliyahu Peter 19-Jan-16 9:45am

I will create one for you, but it will take time...(the original is in hebrew and has some unrelated extras too)...
"Look to my coming, at first light, on the fifth day. At dawn, look to the East."

Richard MacCutchan 19-Jan-16 10:34am

That's Saturday then; don't be late.

Andy Lanng 19-Jan-16 13:42pm

Ha ha. LOTR would have been very different if Gandalph didn't work weekends.
So much info on this I'll have to parse it tomorrow. Thanks everyone!

Kornfeld Eliyahu Peter 19-Jan-16 13:51pm

http://www.codeproject.com/script/Membership/Uploads/3164287/filter.png

BillWoodruff 19-Jan-16 10:30am

Lucky you, this will be not only a challenge but probably keep you employed a while.

"I need a clear mechanism to allow the user to dictate what adjustments need to be made to the data.I need a clear mechanism to allow the user to dictate what adjustments need to be made to the data."

Do you plan on an interface where the end-user can actively select by click-drag, or touch; or, is what you need an interface that essentially lets the user choose a sequence of selection steps, and then choose a sequence of "duplicate" steps ?

Could you describe in more detail what the 'Duplicates xml results in ?

Andy Lanng 19-Jan-16 11:30am

Hah - yeah. I hope to be able to extend this functionality out to all other manner of projects. Essentially I'll be creating an Excel-to-POCO UI that works from anything to anything. It'll be great to have but a bugger to write.

"Duplicate" isn't the correct word. Mapping might be better. I'll have my POCO[] with the base data which I will map to a new POCO[]. Each item in the first array will quite likely end up as two or more in the second.

The best way to describe the Duplicate sections is that it is a "copy to new with these changes" section.

So the base stages are:
1: This row / item (selection+)
2: To these rows / items (duplicate / mapping+) or ignore row
3: With these changes per new item (Adjustments per mapping+)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Ross 2 · Accepted Answer · 2016-01-19T04:07:00

Sounds like you need to be able to
(a) load prior saved rules
(b) save amended rules
(c) allow user to add new rules, and/or edit or remove existing rules
... so, you need a grid of rule definitions, with add/remove buttons and in-grid edit
(d) probably want to save 'who did what' tracking data for when someone claims they didn't mess up a rule change

A rule definition grid entry needs a selection section and an operation section.

The selection section must let the user pick a selector column (pick list of available columns), a comparison operation (drop down list of available options) and a comparison value (text box).
It also must allow the user to add 'OR' clauses. If you need AND as well as OR, you may want to add a graphic in the grid rendering to make the relative grouping of AND and OR clauses clear.

The operation section, well, I couldn't figure out from your XML exactly what operations are permitted, but I expect you can infer from my suggestion for the Selection section how to build a UI for the operations section.

HTH,
Chris

BillWoodruff · Accepted Answer · 2016-01-19T06:30:00

fyi: you might want to read, or post to, the Design and Architecture Forum here; did you notice that Kevin Marois has posted a question there inspired by one of your previous posts ? [^]

The Problem of mapping Rules to Code

Assuming you have created a UI your boss can understand, and you then have a series of rules generated/edited by direct action in that UI (a large assumption !), consider how you might now take action based on those rules to filter the Data.

a. you write your own parser for a unique DSL (domain specific language). that will probably extend your employment by several months. said parser will generate executable code/objects by creating Expression Trees, using Compiler.Emit, and whatever.

b. you map the "rules" by a custom parser to SQL (non-trivial)

c. you find a way to hook into the C# compiler and execute arbitrary code in strings.

c.1 with Visual Studio 2015 and the Roslyn compiler, there is a new scripting facility in the NameSpace: Microsoft.CodeAnalysis.Scripting.CSharp.

c.2 there are numerous examples of scripting for C#; you need to research them. You could even use the 'Eval code in Visual Basic (although, I wouldn't). Check out ReoScript: [^].

User Interface ideas:

a. a few years ago I did a project that looked like this:

a.1 UserControl 'LogicFactory scrollable

a.1.a contains a Panel docked 'Top, with some controls, like a Button to add a new LogicSelector, a Button to evaluate the "results"

a.2 contains a Collection of LogicSelector UserControls, docked 'Top

b. UserControl 'LogicSelector not scrollable

b.1 contains some ComboBoxes whose contents vary with the selections made in the other ComboBoxes. The user can select from various "targets" and select from various operations.

c. how it kind-of worked:

c.1 the user kept adding LogicSelector controls until they had all their choices entered, at some point invoking the "evaluate" facility.

c.2 the evaluate facility iterated over the collection of LogicSelectors and "did the right thing" to act on the "target."

With hindsight: it was a lot of work to write; it was difficult to add new functionality to. The task of keeping all the ComboBoxes in synch with the right Items involved some fancy dancing.

I hope you are aware that the kind of project you describe is of the type (data-mining, decision-tree construction, data analysis) where existing business-logic/rule modeling software costing many thousands of dollars is often used. I would rate the chances of your completing this project to your boss' satisfaction in less than a year as low, based on the inference, from what you say, that the boss really doesn't quite know what he wants.

But, what a great learning opportunity for you :)

Suggested areas for you to research:

1. Decision Tables/Trees

start here: [^]

on CP: [^]

open source: [^] ... lots of others out there

commercial and works with C#: [^] ... others out there

Finally, are you aware there are several powerful (from cheap to pricey) add-ons for Excel where you can do the decision modeling and analysis in Excel ? Suggest you not mention these to boss.

Not a QQ: Need ideas on how to write complete query user interface

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0