logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error
Lists of valid values - sort order, filtering.
Options View
Previous Topic Next Topic
vk4iu
#1 Posted : Wednesday, December 5, 2012 2:04:51 AM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
G'Day,

Today I updated many of my "lists" - JCC, JCG, DOK, Polish provinces and districts.

I updated my JCC and JCG lists for JA QSLs to allow for more "cross checking". That is, the "Descr" field now contains the name of the city and the prefecture. Something like ...

Chiyoda (TOKYO)
or
Kameda (HOKKAIDO) Deleted: Nov.30 1973

Similarly for the German DOK lists, I updated them to things like ...

Offenburg (Baden)

I used Microsoft Access to create each list, and exported a suitable Visual Foxpro DBF file using ODBC. Logic imported the lists OK.

Now ... there is something I do not understand going on.

I flagged all the deleted JCC and JCG using the lists form. I now had a Log form for JA with a user field for JCC, that showed me the data I needed. An award progress displays what I expect, including worked/deleted etc etc.

But, the "deleted" JCC and JCG are all at the top of the list, not in the sorted order by "value". The DXCC list contains deleted entries, but sorts correctly.

What have I missed?

If I try to use a filter on any of the lists, I am able to filter just the JCC list and count the records, but if I also filter on '"deleted"$descr and FIELD_NAME="JCC" the list is empty. A few tests showed that "deleted" is being converted to UPPERCASE and fails to filter the records correctly. This happens with any of the lists. I guess I can live with that.

How do I make the JCC sort just like the DXCC list?

I have performed a "pack and re-index", as well as a "clean" of temporary files. I exported the new JCC list, and re-imported it- no change.

Peter VK4IU
Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
Sponsor
Note: We receive a commission from Amazon when you purchase via this link. It does not affect your cost. Thank you!
Back to top  
vk4iu
#2 Posted : Wednesday, December 5, 2012 3:25:07 AM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
Further to my questions ...

I notice that the default index for the lists table is the tag "DISPLAY" with a value of "FIELD_NAME + IIF(FIELD_TYPE="I", PADL(TRIM(VALUE),LEN(VALUE)), VALUE)" and this is also used in the "log Form" for the drop down lists.

The "value" for JCC is an integer.

While there is a index on the "Descr" field, tag "DESCR", it does not figure in the normal course of events on the lists table.

While better informed I am still none the wiser.

Peter VK4IU
Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
vk4iu
#3 Posted : Wednesday, December 5, 2012 7:05:03 AM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
... I am a little wiser! I hope.

I made a small change to the way I import the JCC.DBF, and "hey presto", it all now works.

To make my JCC list I proceeded as follows ...

To get a feel for what was needed, I did some exports of the existing Logic JCC list from the List form, and loaded the VFP DBF file into a table in a MS 2010 Access database using ODBC import and the ODBC VFP drivers. I have no license for the full implementation of MS Visual Foxpro and its tools.

I obtained an updated JCC list from JARL. Using a text editor and regular expressions, I edited the list into MS Excel to include the data (field_name, value, descr) and the changes I wanted in the description field.

I then experimented a little and took the Excel data across to MS Access and out to a VFP DBF file using MS Access ODBC export with the VFP drivers. I then loaded the data (field_name, value, descr) into Logic with Import List

Logic seemed happy - even though some of the "fields" were not present in the data. I figured the other fields could be updated from the List form in Logic. To a general extent this was true. It all seemed to work - until I ran into the problem with the deleted entries at the top.

So I took a step back and had a good think about the "display" indexing tag and the default sort used for Log forms and user fields with "edited lists". I noted that the "value" field was TEXT. but the "field_type" field said INTEGER. Maybe I have the data type wrong - I thought.

So I added the "field_type" field into my data, making all the records of type I - integer - which was indicated on the Lists form for the original JCC list.

A few minutes later - it all now works correctly. Deleted JCC entries in my newly imported data now sort into their correct position according to value - well, according to the index tag "DISPLAY" ... see Tools, Advanced, Index setup info.

Dennis ... if you can add anything to my method of "importing a new list" into the Lists table I would be very grateful?

What I found seems to contradict the statement in the Comment for the DISPLAY indexing tag for LISTS, that "displays both numeric and character values in proper sequence". More likely - there is a gap in my understanding.

Thanks for your efforts.

Peter VKIU

Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
WN4AZY
#4 Posted : Wednesday, December 5, 2012 11:24:29 AM(UTC)
admin

Rank: Administration

Groups: Administrators, Beta Testers
Posts: 3,061
Man
Location: Auburn, GA

Thanks: 974 times
Was thanked: 486 time(s) in 401 post(s)
Hi Peter:

Thanks for the detailed posts -- you seem to be on top of it now hihi. I haven't read thru all this closely but will do so. I am glad to see someone getting into the internals of LOGic.

I can't say why the deleted showed up on top without seeing the data. Anyway,
Quote:
FIELD_NAME + IIF(FIELD_TYPE="I", PADL(TRIM(VALUE),LEN(VALUE)), VALUE)

just varies the index expression based on whether the field is integer or not. If it is integer, it left-pads the value (or right-justifies in other words). This is so that 2 will come on top of 10 for instance.

There is nothing preventing you from setting up an integer value and entering a character value I suspect the incorrect ordering had something to do with this. The integer values are actually stored in a character field of course. I will add an edit to the Lists screen to check this, but of course this will not prevent importing "bad" data.


As for upper/lower case, if you are using Descr for anything other than something for a human to read, I would make it all upper-case. Go to tools/advanced/database commands. Type:

USE LISTS
REPLACE DESCR WITH UPPER(DESCR) FOR FIELD_NAME="JCC "

But there is a DELETED field -- may as well use it.


I use the free OpenOffice Calc for massaging data, then save as tab-delimited (you can do the same with Excel). Getting the tab-delimited into LOGic is trivial. Go to tools/advanced/database commands. Type:

USE LISTS
APPEND FROM yourfile.txt DELIMITED WITH TAB

to go the other way, type:

COPY TO d:\tmp\jcg.txt FOR FIELD_NAME = "JCG " DELIMITED WITH TAB WITH ""

Click for info on the screwy syntax if you care.



I have had several requests to add generic import to the Lists and Memberships table like the Log has. I think that would be worthwhile.


BTW, if you want to share your tables, feel free to upload them here!

Tnx & 73,

Dennis WN4AZY
Back to top
WWW
vk4iu
#5 Posted : Wednesday, December 5, 2012 4:26:11 PM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
Thanks Dennis.

I shall try the method you describe - using "tab delimited data".

Add yes, the intention was to upload the Lists when I was happy with them.

The funny sort occurs when the "field_type" field is blank for each record. The "deleted records" float to the top of the list when I use the "delete" flag on the List form to flag the record as deleted.

I found the "upper case" issue when I tried filtering the records to make flagging each record as deleted.

I have updated the JCC list. Attached is the JCG.DBF list that exhibits the same problem - records marked deleted float to the top. "field_type" field was blank on import, but changed to I, INTEGER, when a record is flagged as deleted.

Peter VK4IU
File Attachment(s):
JCG.zip (10kb) downloaded 23 time(s).
Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
vk4iu
#6 Posted : Wednesday, December 5, 2012 6:17:00 PM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
G'Day,

Fun and excitment here today at Coolabunia! COPY TO, APPEND FROM works a treat!

I used the "TAB DELIMITED" method. But the data is rather isolated - no field names to help with the organization of the data.

So to eliminate a few steps, I looked up Microsoft's MSDN library and found the correct syntax to use to produce an EXCEL spreadsheet directly from Logic.

At first the process just kept crashing Logic. I searched the Internet again and found that Excel 2003 produced files from Excel 2010 are incompatible with Visual FoxPro. But those saved as Excel 95, XL5 files, are compatible. So I use Excel 2010 and Save As XL5 spreadsheets. The data is simple enough that this should not be a problem.

So I have been using ...
Quote:
COPY/APPEND TO/FROM xxx TYPE XL5


... and it works a treat. In the first row of the spread sheet are the field names. I then use formulas and macros in Excel, whatever, to create the data I need working from the source data to a list in the correct format for Logic.

Clearly, once I have the initial "data set" any small additions are accomplished in the Logic Lists form. It is that inital data set from the Internet data that is the hard part. There are just so many ways that "lists" can be formatted.

Some questions ....

If I drop some of the "fields" from the spreadsheet, what are the implications for Logic. Clearly I can put data into "Comment", "Misc", "unactivated", "deleted".

What about "mult_tag" or "mult_prog". Can I drop "progress(N)", "non_edited". I can see how "non_edited" works.

What happens if I delete a list related to an "edit using list table" user field, say JCC, and re-import the list. What do I loose if these fields are not in the list on reload. Do I have to preserve the values in these fields?

Peter VK4IU


Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
WN4AZY
#7 Posted : Friday, December 7, 2012 10:08:43 AM(UTC)
admin

Rank: Administration

Groups: Administrators, Beta Testers
Posts: 3,061
Man
Location: Auburn, GA

Thanks: 974 times
Was thanked: 486 time(s) in 401 post(s)
Hi--

Quote:
I have updated the JCC list. Attached is the JCG.DBF list that exhibits the same problem - records marked deleted float to the top. "field_type" field was blank on import, but changed to I, INTEGER, when a record is flagged as deleted.


The reason that everything is showing up in a wierd order is that some of the FIELD_TYPE fields have "I" and some are blank. They have to all be "I", regardless of whether they are deleted or not. A rule: the field_type contents must be the same for every record for a given list.

So go to tools/advanced/database commands. Type:

use log
REPLACE ALL field_type WITH "I" for field_name="JCG "

Now you are Golden!

When LOGic imports Tab-delimited it merely goes by column order. The first field of the tab-delimited record goes into the first field, the second in the second, etc. If there are more tab-delimited fields than there are .dbf fields, they extra is discarded. If there are fewer fields in the tab-delimited, the extra dbf fields are not filled in.

I am pretty sure that Excel does the same thing. There is an IMPORT command (Import from file.xls) that creates a .DBF from the XLS, and MAY use the column headings for field names, but I don't recall.

I have never been happy with Excel import. Its handling of dates is horrible.

73,

DH
Back to top
WWW
 |  Edit by user
vk4iu
#8 Posted : Friday, December 7, 2012 7:53:04 PM(UTC)
VK4IU

Rank: Advanced Member

Groups: Moderator, Registered, Administrators
Posts: 404
Man
Location: Hodgleigh QLD

Thanks: 6 times
Was thanked: 77 time(s) in 68 post(s)
Thanks for that Dennis.

I have already updated the JCC field_type column to be the same for all records and things are now soring correctly. And I include the field_type in all the initial lists

I have noted your comments about dates and Excel and Import. So far I don't have data that involves dates in any important way.

My personal lists where I prepare the initial data, contain only the following fields: field_name, value, descr, comment, deleted, misc. The other fields would seem to be recreated by "Update Awards progress". Correct?

This has been a good learning experience for me. I am now much more confortable with lists, initializing them, and importing them into Logic.

I will upload the lists I create for other Logic users.

Thanks again.

Peter VK4IU
Peter VK4IU
You can help by posting images of any errors and including your Logic version.
Back to top
WN4AZY
#9 Posted : Sunday, December 9, 2012 8:03:13 AM(UTC)
admin

Rank: Administration

Groups: Administrators, Beta Testers
Posts: 3,061
Man
Location: Auburn, GA

Thanks: 974 times
Was thanked: 486 time(s) in 401 post(s)
Quote:
My personal lists where I prepare the initial data, contain only the following fields: field_name, value, descr, comment, deleted, misc. The other fields would seem to be recreated by "Update Awards progress". Correct?


Yeah, basically. Technically, the fields are created when you append your data. The values get filled in when you do awards progress update.

Thanks for doing this and helping me and other LOGic users!

74,

Dennis WN4AZY
Back to top
WWW
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF 1.9.5.5 | YAF © 2003-2011, Yet Another Forum.NET
This page was generated in 0.059 seconds.