Alpha Video Training
Results 1 to 18 of 18

Thread: Report to print specific dbf fields to text file

  1. #1
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Report to print specific dbf fields to text file

    Thanks to Billy disavowing winxp recently, I'm trying to move an existing dbf, reports and forms over from A4v8. I have the main form and one report cloned successfully. The mission: I'm trying to print content of exactly 34 fields over to a text file, in a standard order, one field per line. This worked fine in A4, and my program in basic could use a simple batch of input C$ statements in a predictable manner, so Firstname, Lastname, Landline, CellNumber would end up in the correct strings, ready for my manipulations on them to ultimately create an LDIF file.

    Doing the same report in A5V12, I get the data moved up or down into different/wrong strings. Empty fields simply disappear. I worked around that by creating a calc field in the report with a "." inserted if the dbf field happens to be empty. That helps some. The next trick was to try a dummy field at the top of each record to put the word "Start" as the first field for each record in the report. That got me:

    Start
    StartStart
    FirstName
    LastName
    .
    . etc for the first record. The next 3 records have:

    Start
    FirstName
    LastName
    .
    . etc Then it seems to loop back and do it all again.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    Using a report to create a text file probably isn't the best approach.

    Code:
    tbl = table.open("your_table_name")
    tbl.order("field1+field2") 'replace field1+field2 with your desired ordering field or fields
    tbl.fetch_first()
    fil = file.create("outputfilename",FILE_RW_EXCLUSIVE) 'outputfilename should include path, name, and extension
    while .not. tbl.fetch_eof()
    	fil.write_line(tbl.field1) 'replace field1 with actual field name
    	fil.write_line(tbl.field2) 'replace field2 with actual field name
    	fil.write_line(tbl.field3) 'etc
    	fil.write_line(tbl.field4)
    	fil.write_line(tbl.field5)
    	....
    	....
    	fil.write_line(tbl.field34)
    	tbl.fetch_next()
    end while
    fil.flush()
    fil.close()
    tbl.close()
    There can be only one.

  3. #3
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    Now to change the rules...
    I wish to filter the file to only show records where the "Inactive" field is <> "Y" .AND. "VolAppDate" field >"" I've been surfing and playing with this for several days, getting nowhere fast.

    Got your code working well, thanks Stan!

  4. #4
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Report to print specific dbf fields to text file

    Moving from the dos days to windows is a real trip in learning.
    As with most of the people here, I will extend help as you need it.
    Stan is a great help to ALL of us, but many of the things you will be required to do can be handled by using the tools to build your queries, reports, and more. Then you can have the option to get the xbasic code and modify it as needed or wanted. That is how many others do it and then show you the generated code as thought they wrote it(this does not apply to stan and some others).
    I am available by clicking my name and email or call me. I think it is posted.
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    tbl = table.open("your_table_name")
    tbl.query_create("N","Inactive = 'Y' .and. isnotblank(\"VolAppDate\")")
    tbl.fetch_first()

    The rest stays the same.

    tbl.query_create("N","Inactive = 'Y' .and. isnotblank(\"VolAppDate\")"[,"optional order expression would go here"])
    There can be only one.

  6. #6
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    Here is what I now have:

    tbl = table.open("APBVOLS")
    idx=tbl.order("name_sort")

    tbl.query_create("N","inactive<>'Y' .and. isnotblank("volcontrct")")

    tbl.fetch_first()
    '
    fil=file.create("H:\\A4Docs\PassVols.txt",FILE_RW_EXCLUSIVE)
    '
    while .not.tbl.fetch_eof()
    tbl.fetch_next()

    tbl.query_create line now has a red squiggly at front end with "Bad expression character" lurking when I roll over it...

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    tbl.query_create("N","inactive<>'Y' .and. isnotblank("volcontrct")")

    is not what I gave you. Quotes inside quotes are verboten. See the documentation for backslash.

    Forget the table.order() line. Use the second suggestion if you want an order specified.

    tbl = table.open("APBVOLS")
    tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"volcontrct\")","name_sort")
    There can be only one.

  8. #8
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    tbl = table.open("APBVOLS")

    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")","Name_sort")
    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")")
    'tbl.query_create("N","inactive<>'Y'","Name_sort")
    tbl.query_create("N","inactive<>'Y'")
    'tbl.query_create("N","inactive<>'Y' .and. len(\"Volcontrct\")=10")

    tbl.fetch_first()

    fil=file.create("H:\\A4Docs\PassVols.txt",FILE_RW_EXCLUSIVE)

    while .not.tbl.fetch_eof()
    tbl.fetch_next()

    This is working, sort of... Trick is it's not passing all of the records through that DO meet the requirements! I've tried various versions of the filtering part of the query_create line, and the one that's not commented out is the closest to working, but they all get less than the actual records they should. The first one that checks for blank volapps really clobbers the total number of records passed through, about half the number the other versions pass...

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    'tbl.query_create("N","inactive<>'Y'","Name_sort")
    tbl.query_create("N","inactive<>'Y'")
    These are equivalent and return the same number of records but in different order.

    The first one that checks for blank volapps
    None check for blank volapps. They check for blank Volcontrct.
    There can be only one.

  10. #10
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    Agreed on the sort order, and I later found my basic program handles the order, so that doesn't matter here.

    The volcontrct was my terrible choice for a field name in this database, many years back. It really is the date on which our vols filled out our application form. Volcontrct really is a date field in the dbf, MO/DY/YR format.

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    Testing blank date fields is probably better done another way.

    'tbl.query_create("N","inactive<>'Y' .and. alltrim(cdate(volcontrct)) = '' ","Name_sort")


    alltrim(cdate(volcontrct)) = '' <--two single quotes at the end
    There can be only one.

  12. #12
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    tbl = table.open("APBVOLS")

    ' For reference, there are really 6 BMs (Board Members) in our group. All have "N" in "Inactive" field
    ' and "Volcontrct" field has a date...
    '
    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")","Name_sort") 'Yields 2 BMs
    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")") 'Yields 1 BM
    'tbl.query_create("N","inactive<>'Y'","Name_sort")' Yields 4 BMs
    'tbl.query_create("N","inactive<>'Y'")' Yields 1 BM
    'tbl.query_create("N","inactive<>'Y' .and. len(\"Volcontrct\")=10")' Yields 1 BM
    tbl.query_create("N","inactive<>'Y' .and. alltrim(cdate(Volcontrct))= ''","Name_sort")'Yields 0 BMs

    tbl.fetch_first()

    fil=file.create("H:\\A4Docs\PassVols.txt",FILE_RW_EXCLUSIVE)

    while .not.tbl.fetch_eof()
    tbl.fetch_next()

  13. #13
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Report to print specific dbf fields to text file

    Al, the msg board supports private emails. Check and adjust your profile to receive and send them. You can also specify your skype handle if you want. Many of us use skype to talk with one another. If you need additional help you can private msg me here. Or contact me on skype. - tom cone jr

  14. #14
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Report to print specific dbf fields to text file

    Al, when searching for blank date fields, I prefer to use the DTOS() function, instead of DTOC(), for the following reason:

    Code:
    dim curr_date as D 
    
    ?alltrim(dtoc(curr_date))
    
    = "/  /"
    
    
    ?alltrim(dtos(curr_date))
    
    = ""

  15. #15
    Member
    Real Name
    Al Bronson
    Join Date
    Apr 2014
    Posts
    12

    Default Re: Report to print specific dbf fields to text file

    tbl = table.open("APBVOLS")
    ' For reference, there are really 6 BMs (Board Members) in our group. All have "N" in "Inactive" field
    ' and "Volcontrct" field has a date...
    '
    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")","Name_sort") 'Yields 2 BMs
    'tbl.query_create("N","inactive<>'Y' .and. isnotblank(\"Volcontrct\")") 'Yields 1 BM
    'tbl.query_create("N","inactive<>'Y'","Name_sort")' Yields 4 BMs
    'tbl.query_create("N","inactive<>'Y'")' Yields 1 BM
    'tbl.query_create("N","inactive<>'Y' .and. len(\"Volcontrct\")=10")' Yields 1 BM
    'tbl.query_create("N","inactive<>'Y' .and. alltrim(cdate(Volcontrct))= ''","Name_sort")'Yields 0 BMs
    'tbl.query_create("N","inactive<>'Y' .and. alltrim(dtos(Volcontrct))= ''","Name_sort")'Yields 0 BMs

    tbl.fetch_first()

    fil=file.create("H:\\A4Docs\PassVols.txt",FILE_RW_EXCLUSIVE)

    while .not.tbl.fetch_eof()
    tbl.fetch_next()

    In case it's not clear above, I've rerun each of the tbl.query_create lines above, then run my old basic program using the resulting text file, and then added a comment at the end of the query line, showing how many board members it found, of the 6 actual board members. The same basic program works correctly on an old Passvols text file created from the A4v8 version.

  16. #16
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Report to print specific dbf fields to text file

    Al, this should NOT be this difficult.

    We can help you faster with sample data to work with. If privacy is an issue, consider contacting one of us privately. My personal email is tom.cone.jr@gmail.com

    I would expect the following line:

    tbl.query_create("N","inactive<>'Y' .and. alltrim(dtos(Volcontrct))= ''","Name_sort")

    to return records where the inactive field does not have a "Y" in it, AND where the Volcontrct field is blank.

    The following line :

    tbl.query_create("N","inactive<>'Y' .and. alltrim(dtos(Volcontrct))<> ''","Name_sort")

    should return the records where inactive field does not contain a "Y" and Volcontrct is NOT blank

    Do you know how to use the interactive window to test filter expressions such as these?

  17. #17
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Report to print specific dbf fields to text file

    Also you can design a working query on the operations tab, save it, then capture the xbasic behind the operation. You will find the proper query string in that xbasic.
    There can be only one.

  18. #18
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Report to print specific dbf fields to text file

    Quote Originally Posted by Stan Mathews View Post
    Also you can design a working query on the operations tab, save it, then capture the xbasic behind the operation. You will find the proper query string in that xbasic.
    that's great advice Stan as always.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

Similar Threads

  1. Report - Only count fields with specific value, in a group.
    By davidhs in forum Mobile & Browser Applications
    Replies: 3
    Last Post: 08-07-2013, 01:53 PM
  2. Report Help! Print, email, delete Specific report based on selection
    By markbwillard in forum Alpha Five Version 11 - Desktop Applications
    Replies: 12
    Last Post: 02-04-2013, 12:32 PM
  3. print plain text to file
    By drjrjones in forum Alpha Five Version 7
    Replies: 1
    Last Post: 04-03-2006, 12:16 AM
  4. Report - Calc field -Print specific words-
    By TerriH in forum Alpha Five Version 4
    Replies: 4
    Last Post: 05-23-2003, 01:01 PM
  5. Import data from Text File to existing DBF
    By Don Patterson in forum Alpha Five Versions 2 and 3
    Replies: 1
    Last Post: 02-07-2002, 06:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •