Source language: Translate to:

dbpFieldToVar and dbpQuery

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 12:40 am

dbpFieldToVar and dbpQuery not see empty values.

I immediately post the video.
Words can not explain.

File

youtube
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby dpayer » Thu Feb 13, 2014 7:50 am

mishem wrote:dbpFieldToVar and dbpQuery not see empty values.

I immediately post the video.
Words can not explain.

File

youtube


I think the issue is discussed here: viewtopic.php?f=7&t=20159&p=11211342&hilit=variable+empty#p11211342

Empty variables are considered non-existent. You will not see them in the debugger.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 8:14 am

I do not need the debugger.

dbpFieldToVar must copy the records through delimiter.

For example we comma separator.

There are empty record.
Shall return to the variable comma.

Three blank record.
Should return to a variable three commas.

For example there are empty and the next is important.
Returns to the variable value and a comma.

This option works correctly.
And if only empty records, returns nothing.

In my case it is necessary to know exactly have null values ​​or not.

This can be circumvented, and figure out how to calculate.

But in my opinion it should not be.
And of course decide not to me.
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby Neosoft Support » Thu Feb 13, 2014 11:02 am

Have you tried this:

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=,;SkipBlanks=No;NoDuplicates=No"
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 12:38 pm

Yes.
The video shows.

If two empty record shows one.
It does not write the first character.
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby Gaev » Thu Feb 13, 2014 1:18 pm

Mishem:

1) Can you try with another character(s) as the delimiter ? e.g. xxx ... if your Windows is setup to interpret the comma as the decimal delimiter, perhaps that is where the difference is.

2) if that does not solve anything ...

a) can you post the relevant code on the forum (I could not read it in the video) ?

b) if you want me to try and replicate on my machine, using ...

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=,;SkipBlanks=No;NoDuplicates=No"

... please post your results for the different scenarios in this format ...

- number of records
- number of records with blank content
- expected result
- actual result

- number of records
- number of records with blank content
- expected result
- actual result

- number of records
- number of records with blank content
- expected result
- actual result

etc.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 1:41 pm

Required.

This field is only an empty record.
Records can be any number.

I use a splitter

Carriage Return

If you set

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[#13];SkipBlanks=No;NoDuplicates=Yes;DelimiterInContext=Replace"

Not show any entries.

If you set

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[#13];SkipBlanks=No;NoDuplicates=No;DelimiterInContext=Replace"

Show less than one entry.

My code is based on your base.

Code: Select all
dbpSort "AddrBook" "Contacts" "Category=ASC"

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[#13];SkipBlanks=No;NoDuplicates=No;DelimiterInContext=Replace"

hpwLineCount "[Names]" "[LineCount]"


Generally use so.

Code: Select all
dbpSort "AddrBook" "Contacts" "Category=ASC"

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[#13];SkipBlanks=No;NoDuplicates=Yes;DelimiterInContext=Replace"

hpwLineCount "[Names]" "[LineCount]"


If there are no blank entries. Then find the right thing.

Need.
If there is at least one blank record
then
fulfill the condition
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby Gaev » Thu Feb 13, 2014 2:31 pm

mishem:

Thank you for the details.
If you set

dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[#13];SkipBlanks=No;NoDuplicates=No;DelimiterInContext=Replace"

Show less than one entry.
By "less than one entry", did you mean that if there are five records with blank LastName, it shows only four blank entries ? If not, please elaborate.

Regarding the code ...

1) I do not see the property DelimiterInContext=Replace in the Help file ... perhaps I do not have the latest version of the plugin ... can you enlighten me as to what it does ? ... or post the Help file info on this property.

2) Do you get the same results if you use StrParse "[Names]" "[#13]" "[NameItems]" "[LineCount]" in place of hpwLineCount "[Names]" "[LineCount]" ... just to make sure that the string put out by dbpFieldToVar is being correctly interpreted.

Better still, try using the delimiter of "xxx" :wink:

I will advise you of my experience.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 2:42 pm

By "less than one entry", did you mean that if there are five records with blank LastName, it shows only four blank entries ?

Yes.


Help
DelimiterInContext=value
This option determines what to do when the delimiter character appears within the exported data. Value may be one of the following:

Leave
Don't do anything. Leave the data as is.

Replace
Replace the delimiter with spaces.

UseCSVRules
Process the data according to the same rules used when exporting to CSV (comma separated values). Data containing the delimiter will be surrounded by double quotes.


Help

Вы получите те же результаты, если вы используете StrParse "[Имена]" "[№ 13]" "[NameItems]" "[LineCount]"


I know. :)
Just this plugin still works in other places.

He has a great team hpwLine.
And not only ...

I always use this plugin to work with strings.
Very comfortable.

I will advise you of my experience.

Thank you.
In my case, I found a workaround.

Such messages are writing to know.
If it does not know the end result may be wrong. :(
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 5:41 pm

In my case, I found a workaround.


But there was another problem.

If the entry is empty and has not changed it will not appear when searching

Code: Select all
dbpQuery "AddrBook" "Contacts" "LastName=[#34][#34]"

.............................
Help to read.
:)

That's how it works.

Code: Select all
dbpQuery "AddrBook" "Contacts" "LastName IS NULL"
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby Gaev » Thu Feb 13, 2014 8:14 pm

Mishem:

I found the problem ... but it is NOT with the dbpFieldToVar command ... rather, the way the StrParse command interprets the results.

I took the Contacts database table that comes with the plug-in ... and added 2 records ... FirstName of "NoLastName" and "NoLastName2' ... and LastName being empty.

There is a record for Nancy True ... so I use the query to select records with FirstName beginning with N ... which correctly shows 3 records.

Using the delimiter of "xyz", [Names] contains "Truexyzxyz" ... which is correct.

But the [ItemCount] variable is 2 !!! ... should be 3.

I used [#13] for [Delimiter] and got same results.

I think this is because when StrParse finds nothing/null after the last delimiter, it does not count it as an empty item. I think changing the logic to count it as an extra item would break existing Applications ... so best solution for you might be to check if the last character in the FieldToVar variable is the delimiter ... and if so, add one to your count.

Here is the full code ...
Code: Select all
dbpOpenAccessDatabase "AddrBook" "!C:\gk\Neo5\DBProSamples\AddressBook\AddressBook.mdb" ""
dbpOpenTable "AddrBook" "Contacts" ""

dbpQuery "AddrBook" "Contacts" "FirstName LIKE [#34]N%[#34]"

dbpShowGrid "AddrBook" "Contacts" "Rectangle5"

SetVar "[Delimiter]" "xyz"
dbpFieldToVar "AddrBook" "Contacts" "LastName" "[Names]" "Delimiter=[Delimiter];SkipBlanks=No;NoDuplicates=No;DelimiterInContext=Replace"

StrParse "[Names]" "[Delimiter]" "[NameItem]" "[ItemCount]"
AlertBox "Names" "[ItemCount] items ... [Names]"

dbpCloseTable "AddrBook" "Contacts"
dbpCloseDatabase "AddrBook"


P.S. same problem when query is "LastName IS NULL"
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 9:25 pm

Also a good option.
But I did so.

Code: Select all
dbpQuery "AddrBook" "Contacts" "LastName IS NULL"
If "[AddrBook.Contacts.$RecCount]" "<>" "0"
........................


In $RecCount count the number of empty records.
And now more able to do what you need.
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm

Re: dbpFieldToVar and dbpQuery

Postby mishem » Thu Feb 13, 2014 9:34 pm

I do not use StrParse.
If interested.
Below is the code that I needed.

To get a list of in hpwTreeView

Code: Select all
dbpShowAll "Shop" "V"
dbpRefresh "Shop" "V"

SetVar "[Category_List]" "Все"

dbpSort "Shop" "V" "Category=ASC"


dbpQuery "Shop" "V" "Category IS NULL"
If "[Shop.V.Code]" "<>" ""
    SetVar "[Category_List]" "[Category_List][#13][#9]Нет категории"
EndIf
dbpShowAll "Shop" "V"

dbpFieldToVar "Shop" "V" "Category" "[Category_All]" "Delimiter=[#13];SkipBlanks=Yes;NoDuplicates=Yes;DelimiterInContext=Replace"

hpwLineCount "[Category_All]" "[LineCount]"
If "[LineCount]" ">" "0"
    Loop "1" "[LineCount]" "[i]"
        hpwLine "[Category_All]" "[i]" "[Category]"

        SetVar "[Category_List]" "[Category_List][#13][#9][Category]"

        dbpSort "Shop" "V" "Category=ASC;Grup=ASC"

        dbpQuery "Shop" "V" "Category=[#34][Category][#34]"
        dbpFieldToVar "Shop" "V" "Grup" "[Grup_All]" "Delimiter=[#13];SkipBlanks=Yes;NoDuplicates=Yes;DelimiterInContext=Replace"

        hpwLineCount "[Grup_All]" "[LineCount_Grup]"
        If "[LineCount_Grup]" ">" "0"
            Loop "1" "[LineCount_Grup]" "[J]"
                hpwLine "[Grup_All]" "[J]" "[Grup]"
                SetVar "[Category_List]" "[Category_List][#13][#9][#9][Grup]"
            EndLoop
        EndIf
        dbpShowAll "Shop" "V"
    EndLoop
EndIf

FileWrite "[PubDir]TreeList" "All" "[Category_List]"
mishem
 
Posts: 576
Joined: Mon Oct 08, 2012 1:51 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests