• Registered: 2017-04-27
  • Posts: 81

Topic: Find data In Range And Calculate + Compare

Hello Community,

I was trying to find data in between the given range from the item table

example in between range is 1.20 to 1.232

from table item list is

item           range

item1        1.21
item2        1.231

so in calculated data it will show the result in particular range 2 item (not able to solve)

Now issue 1 I was not able to add more than 2 decimal point if i add 1.231 it saves as 1.23 tried changing field with real and currency changed decimal points but still it store only 2 decimal point

Also If range has its value than I want to multiply its value with current available item.

So if 1.20 to 1.232 has value of 200 than and found items were 2 so calculated value is 400.

I like to store value up to 4 decimal point Example "4.2310" in database and like to show same in grid.

I also tried mask to store value in edit box but was not able to do the way I want.

How to do this example like "1.2356 - 1.2452" but without restriction of adding extra value at both site of "-"

so when i Added 0000-0000   i was stopped at 1234-1234 but i want to add 12345-1234 than i Have to change it again, so i tried to validate using similar email syntax like A@A (was not able to do it).   sad

I also attached excel file so anyone who want to help can understand what I am trying here.

Excel file is in zip file

thanks

Post's attachments

Attachment icon Compare Find And Calculate.zip 346.48 kb, 420 downloads since 2017-05-08 

  • Registered: 2015-11-13
  • Posts: 652

Re: Find data In Range And Calculate + Compare

Kim, As far as your decimal points are concerned, if you change the field type to REAL in the Database Tables tab within MVD, you should be able to enter in decimals past 2. I changed it in your project you attached and I was able to enter in values such as 1.2345 and it showed that way in the grid.


Forgive me for my denseness. I'm trying to understand the range searches and the calculations you want. It might help if you would do a screenshot of your form and make annotations on it and submit it here so we can get a better feel for how the form should work.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,685

Re: Find data In Range And Calculate + Compare

Hi Kim, Hi EHW,
You beat me to it, EHW - I was about to say the same thing about defining fields as 'real'.
With regard to your other issue, I see what you are trying to do but I think you'll have to:
1)  define the lower and upper values of each range (both old and new) as discrete fields - trying to determine which item falls within which range when the lower and upper range is defined in 1 text field will be very tricky.
2)  perhaps use calculated fields to determine which old range and which new range an item falls into, what the old calculated value is, what the new calculated value is and the difference between the two.  This could then be displayed in a tablegrid with totals.
(By the way, I'm presuming that your ranges do not overlap otherwise the calculation would not work).
Derek.

  • Registered: 2017-04-27
  • Posts: 81

Re: Find data In Range And Calculate + Compare

Hi @ehwagner thanks for reply I annotated image as per your suggestion thanks for helping me out. It is in "Image" folder inside zip file.

ehwagner wrote:

Kim, As far as your decimal points are concerned, if you change the field type to REAL in the Database Tables tab within MVD, you should be able to enter in decimals past 2. I changed it in your project you attached and I was able to enter in values such as 1.2345 and it showed that way in the grid.


Forgive me for my denseness. I'm trying to understand the range searches and the calculations you want. It might help if you would do a screenshot of your form and make annotations on it and submit it here so we can get a better feel for how the form should work.

Post's attachments

Attachment icon Compare Find And Calculate.zip 453.23 kb, 384 downloads since 2017-05-09 

  • Registered: 2017-04-27
  • Posts: 81

Re: Find data In Range And Calculate + Compare

Thanks @derek I attached annotated images to your reply I was able to do grouping in excel and just do pivot to find out what i want but with MVD its kind of tricky. 

derek wrote:

Hi Kim, Hi EHW,
You beat me to it, EHW - I was about to say the same thing about defining fields as 'real'.
With regard to your other issue, I see what you are trying to do but I think you'll have to:
1)  define the lower and upper values of each range (both old and new) as discrete fields - trying to determine which item falls within which range when the lower and upper range is defined in 1 text field will be very tricky.
2)  perhaps use calculated fields to determine which old range and which new range an item falls into, what the old calculated value is, what the new calculated value is and the difference between the two.  This could then be displayed in a tablegrid with totals.
(By the way, I'm presuming that your ranges do not overlap otherwise the calculation would not work).
Derek.

Post's attachments

Attachment icon image.zip 106.56 kb, 384 downloads since 2017-05-09 

6 Reply by kim143 (edited by kim143 2017-05-09 18:40:59)

  • Registered: 2017-04-27
  • Posts: 81

Re: Find data In Range And Calculate + Compare

Hello community,

I used query in "populate" button to populate data in grid5( works perfectly)

And I was trying to do same with calculated field in "compare" table but I was not able to do it.

Main purpose of query is to extract two values and store it in 2 different calculated field.

Example 1.20-1.29

val1 will be 1.20
val2 will be 1.29

in two different calculated field.

My issue is it does perform calculation on first field after that it stuck with last value of "instr" in my case it is 4 i think.


thanks for help.

Post's attachments

Attachment icon Compare Find And Calculate (2).zip 453.57 kb, 406 downloads since 2017-05-09 

  • Registered: 2015-11-13
  • Posts: 652

Re: Find data In Range And Calculate + Compare

Kim,  to format your values in the grid out to 4 decimal places, create an onChange event for your tablegrid1 and place the following formatmask statement:


procedure Form1_TableGrid1_OnChange (Sender: string);
begin
    TNxNumberColumn(Form1.TableGrid1.Columns[1]).FormatMask := '#0.0000';
end;

Question - do your ranges need to be stored in one field? Can they be split into two fields?


Derek or Dimitry - chime in anytime. I don't want to step on any toes and my time is somewhat limited, but I'll do what I can.

  • Registered: 2015-11-13
  • Posts: 652

Re: Find data In Range And Calculate + Compare

Kim,  I made an attempt to put together something you are looking for. I used your project in a prior post. I didn't realize that you had an updated project. So hopefully I'm not way off. But if I am, maybe you can use parts of what I put together. The one thing I would caution you on is the freeform style of the ranges. Since your ranges usually have spaces around the dash, the script is counting on that format. Obviously one can program around that, but I didn't have any more time to spend on it for now. Check out the attached project and see if you can use any of it.

Post's attachments

Attachment icon Compare Find And Calculate Revised.zip 701.69 kb, 525 downloads since 2017-05-10 

  • Registered: 2017-04-27
  • Posts: 81

Re: Find data In Range And Calculate + Compare

Thank you very much @ehwagner your help is really appreciated.