Selecting specific record number and field in a table

[Ted Giles]

This is an added complication from my post about selecting a specific cell in an Excel sheet. It works but there could be an easier way.

The data is imported from a CSV provided by HMRC.
There are 14 records for each CSV document
There are 10 fields per record - many are blank

To display a report correctly I need to select the fields in an order which is different to the one imported.

So, (Record3, Field2) and (Record5, Field9) is what I'm after as an example. In all, around 25 fields need to be picked up from different record number and fields within that recno.
I tried using a Calculated Field on the report to snag the data I am after, but I am not being too successful.

Can someone point me in the right direction please?

[Ray in Capetown]

Re: Selecting specific record number and field in a table

Hi there Ted
I would first try approach this problem using these two aspects.
1. Collect the whole range of data using this function from Tom Henkel helped by Stan, http://www.alphasoftware.com/alphafo...-Not-1st-Sheet
2. String Dictionary which is really quick even across large samples (1000's), to associate the cells you want in the specified order.

If you wish, I can put in some work together with you to run this through if nothing else better comes up here.

Added
You may already have phase one available. The second aspect is very suitable to the ordering (and assessing if a valid entry exists)
you can then just use the SD in the reports.

[Ted Giles]

Re: Selecting specific record number and field in a table

Thanks Ray.
I have the data as a csv import to a table.
I can select and set variables to the values anywhere in the spread sheet - there is only one.
So its working.
What I am after is the logic to select a field within a record number.
Think of it as an array.
Record 1 is the A axis, 2 is the B axis etc
Each axis has 10 elements.
I want to select A5, B9 say.
Maybe loading the table into an array is the answer, but I'm not sure how to go about that.
Then possibly I can pick Array A5 etc.

[Stan Mathews]

Re: Selecting specific record number and field in a table

<TBL>.FIELD_GET() accepts a field name or position number () and returns a pointer to the field. 1 to however many fields there are, left to right.

<fld>.value_get() will return the contents of the field.

"(Record3, Field2)"

tbl = table.open("dataf")
tbl.fetch_goto(3)
fld = tbl.field_get(2)
? fld.value_get()
= {04/23/2015}

tbl.close()

[Ted Giles]

Re: Selecting specific record number and field in a table

Thanks Stan.
Will this logic work in a calculated field?

[Al Buchholz]

Re: Selecting specific record number and field in a table

Thanks Stan.
Will this logic work in a calculated field?

Make a Udf.

[Stan Mathews]

Re: Selecting specific record number and field in a table

FUNCTION fld_contents AS A (tblname AS C, fldnum AS N, rec AS N )
	tbl = table.open(tblname)
	tbl.fetch_goto(rec)
	fld = tbl.field_get(fldnum)
	fld_contents = fld.value_get()
	tbl.close()
END FUNCTION

? fld_contents("dataf",2,3)
= {04/23/2015}

[Ray in Capetown]

Re: Selecting specific record number and field in a table

I was probably reading too much into the "added complication" that the data once obtained needs to comply with a prearranged but different selection and order for each into a report.

[Ted Giles]

Re: Selecting specific record number and field in a table

Thank you Stan, that UDF works very well. The imported CSV content using record number and field position can be accessed using the function.
The only issue outstanding is this;
Date format in the CSV is yyyymmdd - all csv fields are character.
In Excel it transposes to is ddmmyyyy (which is what I need).
Although there is only one date, it is important for tax purposes and needs to be ddmmyyyy format.
So record 6, field 1 is stored as yyyymmddd so how would I go about changing that one?

The alternative would be to import the Excel file, and I can do that but it's an added process.

[Ray in Capetown]

Re: Selecting specific record number and field in a table

Stan's still sleeping Ted so how about identify the field as a date depending on other field content then
after the line
fld_contents = fld.value_get()
add 3 lines..

if [I]fld_contents is date string[/I] 'eg.  rec=6 .and. fldnum=1
    fld_contents=substr(fld_contents,9,2)+substr(fld_contents,5,4)+left(fld_contents,4)
end if

[Ted Giles]

Re: Selecting specific record number and field in a table

I am indebted, Ray.
However, suddenly my problem has disappeared.
The CSV now shows dates in ddmmyyyy format so I'm back on track.

[Ray in Capetown]

Re: Selecting specific record number and field in a table

I strongly suspect, since you are using CSVs that result from Excel, that the CSVs you get may be prepared from different desktops that have not been uniformly setup for country. This results in Excel using different formats for both date and currency when writing to the CSV.
You may need further interrogation of the two fields, checking which format is used for the current CSV.

[Ted Giles]

Re: Selecting specific record number and field in a table

Other way around Ray.
HMRC create the csv. I create the Excel sheet.