none
how to filter data on report using vfp 6.0

    Question

  • good day sir/mam got trouble on displaying value on visual fox pro 6.0 report. I don't want my report to display blank date like this  / / and 0.00 for numbers how can I display them blank on my report but still display other data on that row? I try SET FILTER TO but it doesn't work for me because it hide everything. please help thanks...

     
    Monday, July 22, 2013 1:47 AM

Answers

All replies

  • You have to use the option print when For dates : not empty([field]) For numbers: [field] > 0 You can also use the IIF() statement in the expresion
    Monday, July 22, 2013 3:14 AM
    Answerer
  • hi there Benny thanks for reply I'm just a beginner don't really get your answer. this is what I do to filter specific persons record in print botton, where do I need to set IIF() statement on a form/printButton? can you teach me the codes on how leave it blank just look on my example below

    select svc_rec
    locate for thisform.container1.text1.value=link
    if found()
        set filter to link = thisform.container1.text1.value  && personnel ID No
        set order to indicator && indicator to arrange dates  
        report form "e:\legacy\report\service_record" PREVIEW
    else
        messagebox("No Record Found",64,"SYSTEM")
    endif

    SERVICE

    RANK

    STATUS

    SALARY

    FROM

    TO

    02/21/2003

    02/20/2004

    PO1

    TEMP

    8,065.00

    02/21/2004

    06/30/2008

    PO1

    PERM

    8,065.00

    Granted RCA CY 2006

     

     

    0.00

    Granted 1<sup>st</sup> Long Pay

     

     

    0.00

    07/01/2008

    12/30/2008

    PO1

    PERM

    9,466.00

    Granted RCA CY 2009

     

     

    0.00

    12/31/2008

    06/30/2009

    PO2

    PERM

    10,635.00

    07/01/2009

    06/23/2010

    PO2

    PERM

    12,210.00

    06/24/2010

    12/30/2011

    PO2

    PERM

    13,785.00

     I cheat I put 2 fields, one that display dates and the other one is type which display in front of dates when needed. when you look on table, salary is blank but how come it put 0.00 on print report:

    if thisform.pageframe2.page4.container1.text3.value != 0  && ex. on adding the salary to my table
        repla salary with thisform.pageframe2.page4.container1.text3.value
    endif

    pls help thanks

    Tuesday, July 23, 2013 2:29 AM
  • Jocajosh,

    I still don't know your tables fields/data, but I'll give you some advise/guidance the best of my possibilities.

    If you filter the table before the report, you will hide out the (unwanted) records, for what I understood from your email, you want to show some records with empty boxes for when it is not filled in

    Look at my 3 next pictures, hope you'll get the answer from there

    1) I started with a tem  table and the following information. A report will look like follows

    2) For the fldate (date field), I set the expression

    IIF(EMPTY(FLDATE), "", FLDATE)    .... If there is not date, leave blanks, otherwise print the date,

    For the Salary field, I am showing how I am setting the box

    Tuesday, July 23, 2013 2:11 PM
    Answerer
  • Wow thanks a lot Benny you just solve my problem on numbers and date. I will quote on your answer "If you filter the table before the report, you will hide out the (unwanted) records". I only use it to filter specific records of my table and put them on print report just like on my example but the problem is after I successfully print a record seems the data of the table are hiding what do you think are the cause? But if I don't filter them all records in the table will be printed =(

         Actually got many report need to be print but after I successfully done one print seems the whole table and the others are missing. do you think the SET FILTER are causing this problem? got stuck on this for a month and still don't get the solution for it.

    Wednesday, July 24, 2013 1:40 AM
  • Do you call any procedure from inside the report, that might affect the values on the table?

    You mentioned you have two set of fields, are you doing the proper replacement? I would eliminate the two sets, and only work with the original.

    Wednesday, July 24, 2013 3:02 AM
    Answerer
  • I never use any procedure inside my reports, what I do is I insert a table on data environment on each report. on the first report I name it service_record.frx which use svc_rec.dbf then put a command button Print name "Service Record" inside of that button:

    select svc_rec
    locate for thisform.container1.text1.value=link
    if found()
        set filter to link = thisform.container1.text1.value  && link is personnel ID No
        set order to indicator && indicator to arrange dates  
        report form "e:\legacy\report\service_record" PREVIEW
    else
        messagebox("No Record Found",64,"SYSTEM")
    endif

    after I print and return to my form it works fine. Next is I click the other print button which I name it "Duty Status" I do what I do in my first report like duty_status.frx and use pdatasheet.dbf on its data environment

    select pdatasheet
    go top
    locate for thisform.container1.text1.value=link
    if found()
        set filter to link = thisform.container1.text1.value
         report form "e:\legacy\report\duty_status" PREVIEW
    else
        messagebox("No Record Found",64,"SYSTEM")
    endif

    and it can print also but the problem is after I go back to my form it can't see any tables both pdatasheet.dbf and  service_rec.dbf and other tables? the only record can search is my last record that I search. other command such as search button tells me that no files found do you think it hides/locked all the tables

    Note: pdatasheet.dbf holds all data information of all personnel and svc_rec.dbf holds Service records of each personnel. I'm really confuse thanks for your time reading it.

    Wednesday, July 24, 2013 4:46 AM
  • I would suggest couple of things

    1) I would put the SET ORDER TO INDICATOR ar the beginning, and would just do SEEK, instead LOCATE, for pdatasheet, I would also create an index.

    2) REPORT FORM, at the end of the report, send the cursor to End Of File, if you have your form where the controlsource are pdatasheet->field name, after the first report you wont see any difference, because is not the table linked, however on the second report, displays might changed.

    What I would do in your case, depending the size of the table (the amount of records), There is a big difference between SELECT * ..... and COPY WHILE ... , since the SELECT evaluates the entire table.

    Once the record is found, I would COPY WHILE link = thisform.container1.text1.value to <temp file> and do the report form from the temp file, this way you don't have to worry on changing pointers. 

    Try changing the report to the temp file, my feeling is your form is loosing the pointer on your table

    Wednesday, July 24, 2013 1:18 PM
    Answerer
  • I see so the pointers cause it I will try to make a dummy table and copy those record that I need from the original to that table I already done that on my other example. thanks for the info.
    Thursday, July 25, 2013 1:10 AM
  • No need for a dummy table or copying records. Use SQL-SELECT to create a cursor (temporary table) that contains the records you want. That's what most of us do before running a report.

    Tamar

    Thursday, July 25, 2013 8:28 PM
    Answerer
  • Tamar, you are correct, however, as I mentioned before (on my post from July 24, 2013 1:18 PM) on a large table could be slow the SQL SELECT, in those cases COPY WHILE works better.
    Thursday, July 25, 2013 8:32 PM
    Answerer
  • Tamar, you are correct, however, as I mentioned before (on my post from July 24, 2013 1:18 PM) on a large table could be slow the SQL SELECT, in those cases COPY WHILE works better.

    No, if you're ending up with the same set of records, and you have the right tags for optimization, it's very unlikely that copying to a physical table will be faster than a SQL SELECT. If the result set is large, both are likely to create a real file and the time to do that will far outweigh the time to access the right records. If the result set is small and the query optimizable, the query probably won't actually write to the disk and thus should be much faster.

    Tamar

    Friday, July 26, 2013 8:35 PM
    Answerer
  • Sorry proposed as answer by mistake but the discussion is valuable.

    I would not suppose the set of records for report is too large...

    Even the copy to file uses Windows buffers...  SEEK and COPY WHILE is always faster than SQL SELECT when appropriate index exists. Engine does not need to decide about optimization when you are just seeking. Of course, other factor can affect the speed, e.g. number of deleted records and SET DELETED + INDEX ON DELETED() etc.

    But the fastest is SET KEY. You don't need to SEEK, you don't need to SELECT, engine does not need to decide about optimization...

    OTOH, SQL SELECT does not evaluate the whole table because it reads the index first. SQL SELECT allows to "encapsulate" the data complexity and relations into one command. And SQL SELECT is the way which is simply portable to any SQL engine if you plan it in the future.

    I am using SQL SELECT to prepare data in almost all reports but some columns are still updated in a SCAN loop...


    Saturday, July 27, 2013 9:57 AM
    Moderator