Announcement

**Tom Cone Jr** · 06-26-2009, 06:21 PM

Re: Filtering child values

Sean, for future reference you should note that the message board supports formatted code, which is much easier to read than everything flush left. Use the "Go Advanced" button to get to a better editor for your reply, then select the xbasic code in your post, then click the "code" tool. Code tags will be inserted at beginning and end of your selected xbasic code and your existing formatting will be preserved. Otherwise the msg board slams everything to the left margin and we can't read it.... at least not easily.

**Firemedic934** · 06-26-2009, 06:45 PM

Re: Filtering child values

is this any better.

Im trying..

thanks for your reply..

Sean..

Code:

'Date Created: 18-Jun-2009 06:02:02 PM
'Last Updated: 26-Jun-2009 06:07:29 PM
'Created By  : Sean Werner
'Updated By  : Sean Werner
' NEMSIS DEMOGRAPHIC DATASET XML EXPORT'

tbl=table.open("D01")
fil = file.create(a5.get_path()+chr(92)+alltrim(tbl.Emsagencynumber)+"_demographic"+time("_yyyyMMdd")+".xml",FILE_RW_EXCLUSIVE)
fil.write_line("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>")
fil.write_line("<EMSDataSet xmlns=\"http://www.nemsis.org\"")
fil.write_line(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"")
fil.write_line(" xsi:schemaLocation=\"http://www.nemsis.org")
fil.write_line(" http://www.nemsis.org/media/XSD/EMSDemographicDataSet.xsd""\">")
tbl = table.open("d01")
fil.write_line(" <D01 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
fil.write_line("      <D01_01>"+alltrim(tbl.Emsagencynumber)+"</D01_01>")
fil.write_line("      <D01_02>"+alltrim(tbl.Emsagencyname)+"</D01_02>")
tbl = table.open("emsagencystate")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D01_03 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.fipscode)+"</D01_04>")
	tbl.fetch_next()
END WHILE
tbl = table.open("emsagencycounties")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D01_04 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.Emsagencyfipscode)+"</D01_04>")
	tbl.fetch_next()
END WHILE
tbl = table.open("d01")
fil.write_line("      <D01_05>"+alltrim(tbl.D01_05)+"</D01_05>")
tbl = table.open("othertypeofservices")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D01_06 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.d01_06)+"</D01_06>")
	tbl.fetch_next()
END WHILE
tbl = table.open("d01")
fil.write_line("      <D01_07>"+alltrim(tbl.D01_07)+"</D01_07>")
fil.write_line("      <D01_08>"+alltrim(tbl.D01_08)+"</D01_08>")
fil.write_line("      <D01_09>"+alltrim(tbl.D01_09)+"</D01_09>")
fil.write_line("      <D01_09>"+alltrim(tbl.D01_09)+"</D01_09>")
fil.write_line("      <D01_10_0>")
fil.write_line("              <D01_10>"+alltrim(tbl.Statsticalyear)+"</D01_10>")
fil.write_line("              <D01_12>"+alltrim(tbl.Total_service_area_population)+"</D01_12>")
fil.write_line("              <D01_13>"+alltrim(tbl.Total_service_area_population)+"</D01_13>")
fil.write_line("              <D01_14>"+alltrim(tbl.A11_call_volume_per_year)+"</D01_14>")
fil.write_line("              <D01_15>"+alltrim(tbl.Ems_dispatch_volume_per_year)+"</D01_15>")
fil.write_line("              <D01_16>"+alltrim(tbl.Ems_transport_volume_per_year)+"</D01_16>")
fil.write_line("              <D01_17>"+alltrim(tbl.Ems_patient_contact_volume_per_y)+"</D01_17>")
fil.write_line("              <D01_18>"+alltrim(tbl.Ems_billable_calls_per_year)+"</D01_18>")
fil.write_line("      </D01_10_0>")
tbl = table.open("otheremsagencies")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D01_11 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.Otheremsagencies)+"</D01_11>")
	tbl.fetch_next()
END WHILE
tbl = table.open("d01")
fil.write_line("              <D01_19>"+alltrim(tbl.D01_19)+"</D01_19>")
fil.write_line("              <D01_20>"+alltrim(tbl.D01_20)+"</D01_20>")
fil.write_line("              <D01_21>"+alltrim(tbl.National_provider_identifier)+"</D01_21>")
fil.write_line("</D01>")
fil.write_line(" <D02 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
fil.write_line("      <D02_01>"+alltrim(tbl.Agency_Contact_Last_Name)+"</D02_01>")
fil.write_line("      <D02_02>"+alltrim(tbl.Agency_Contact_Middle_Name_Initi)+"</D02_02>")
fil.write_line("      <D02_03>"+alltrim(tbl.Agency_Contact_First_Name)+"</D02_03>")
fil.write_line("      <D02_04>"+alltrim(tbl.Agency_Contact_Address)+"</D02_04>")
fil.write_line("      <D02_05>"+alltrim(tbl.D02_05)+"</D02_05>")
fil.write_line("      <D02_06>"+alltrim(tbl.D02_06)+"</D02_06>")
fil.write_line("      <D02_07>"+alltrim(tbl.Agency_contact_zip_code)+"</D02_07>")
fil.write_line("      <D02_08>"+alltrim(tbl.Agency_contact_telephone_number)+"</D02_08>")
fil.write_line("      <D02_09>"+alltrim(tbl.Agency_contact_fax_number)+"</D02_09>")
fil.write_line("      <D02_10>"+alltrim(tbl.Agency_contact_email_address)+"</D02_10>")
fil.write_line("      <D02_11>"+alltrim(tbl.Agency_contact_web_address)+"</D02_11>")
fil.write_line("</D02>")
fil.write_line(" <D03 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
fil.write_line("      <D03_01>"+alltrim(tbl.Agency_medical_director_last_nam)+"</D03_01>")
fil.write_line("      <D03_02>"+alltrim(tbl.Agency_medical_director_middle_n)+"</D03_02>")
fil.write_line("      <D03_03>"+alltrim(tbl.Agency_medical_director_first_na)+"</D03_03>")
fil.write_line("</D03_01_0>")
fil.write_line("<D03_04_0>")
fil.write_line("      <D03_04>"+alltrim(tbl.Agency_medical_director_address)+"</D03_04>")
fil.write_line("      <D03_05>"+alltrim(tbl.D03_05)+"</D03_05>")
fil.write_line("      <D03_06>"+alltrim(tbl.D03_06)+"</D03_06>")
fil.write_line("      <D03_07>"+alltrim(tbl.Agency_medical_director_zip_code)+"</D03_07>")
fil.write_line("</D03_04_0>")
fil.write_line("      <D03_08>"+alltrim(tbl.Agency_medical_director_telephon)+"</D03_08>")
fil.write_line("      <D03_09>"+alltrim(tbl.Agency_medical_director_fax_numb)+"</D03_09>")
fil.write_line("      <D03_10>"+alltrim(tbl.D03_10)+"</D03_10>")
fil.write_line("      <D03_11>"+alltrim(tbl.Agency_medical_director_email_ad)+"</D03_11>")
fil.write_line("</D03>")
tbl = table.open("emsagencylevels")
fil.write_line("<D04>")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D04_01 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.D04_01)+"</D04_01>")
	tbl.fetch_next()
END WHILE
tbl = table.open("emsagencycallsigns")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D04_02 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.Emsunitcallsigns)+"</D04_02>")
	tbl.fetch_next()
END WHILE
tbl = table.open("zones")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D04_03 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.zonename)+"</D04_03>")
	tbl.fetch_next()
END WHILE
tbl.open("procedures")
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line(" <D04_04_04 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">")
	fil.write_line("      <D04_04>"+alltrim(tbl.D04_04)+"</D04_04>")
	tbl.fetch_next()
END WHILE
tbl.open("d04_05")
filter = tbl.D04_04
query.filter ="filter"
query.order ="filter"
tbl.fetch_first()
WHILE .not. tbl.fetch_eof()
	fil.write_line("      <D04_05>"+alltrim(tbl.D04_05)+"</D04_05>")
	tbl.fetch_next()
end while

**Stan Mathews** · 06-26-2009, 09:18 PM

Re: Filtering child values

More readable but still obscure. You mention filtering data based on a set but you are only using table.open() statements. Nothing you have shown indicates any filter or uses a set.

In the code you indicate you have multiple tbl=table.open("D01") statements but nothing ever closes "D01". Opening a table pointer without first closing the previous instance can lead to disaster.

I believe you are going to have to explain a bit more about the set structure and how you intend to process it.

**Firemedic934** · 06-27-2009, 07:40 AM

Re: Filtering child values

Thanks for your comments. I am thankful for the help.. I am new to the xbasic and trying to figure things out.. I will close the open tables after each open.table statment..

What i am trying to accomplish is this.. I have a set named procedures.. In the first table there is a procedure name.. then there is a code that goes with the procedure.. I have another table named personnelpermitted.. This table contains the D04_04 which is the procedure code. this is linked to the procedure table. The set is used, so that each procedure can have multiple personnel attached to it.. For example if i have a procedure named iv access, i will have multiple personnel attached to it.. So for example.. Nurse and Physican can do the iv access. When i export the data i need the data filtered by procedure and the personnel permitted to use the procedure...

the output would look something like this..

Procedure Name
Personnel permmited to use the procedure.

I tried using the set.fetch_next statments and the set.open commands without sucess. this is where i am stuck..

I thank you for your help..

sean..

**Tom Cone Jr** · 06-27-2009, 09:38 AM

Re: Filtering child values

Sean, if your goal is to produce a written report you're working way too hard. Just preview a report and save it as a text or pdf file.

OTOH if you need to create a text file a typical sequence would be this:

a) open the primary table in your set then select the index on your procedure_code field
b) open the one to many child table, then select the index on your procedure_code field
c) fetch the first record in both tables
d) Use While ... end while loop to fetch through the parent table records
e) On each fetch in the first (outer) loop fetch the corresponding record in the child table and store the code in a variable
f) Use second (inner) While ... end While loop to fetch through the child table records until the code in a fetched record no longer matches the variable, or you fetch beyond end of file
g) when the code in the child table record fails to match the variable, go to the outer loop and fetch the next parent record, then repeat the pattern over and over until there are no more parent records to fetch.

**Firemedic934** · 06-27-2009, 11:22 AM

Re: Filtering child values

Thanks,

I am trying to understand the fetching. I am also trying to open the index on the tables. I am running in to difficulty doing that.

do i use the indx commands or create the index in each table?

**Tom Cone Jr** · 06-27-2009, 12:00 PM

Re: Filtering child values

You might look for examples on the message board by searching for threads that include "fetching" and "child records". I believe this kind of thing has been discussed before in the context of copying child table records to an external table. While the copying of the records is different from your need, the fetching of consecutive groups of similar records is exactly what you need.

**Firemedic934** · 06-27-2009, 12:00 PM

Re: Filtering child values

here is the code that i have so far.

Code:

tbl = table.open("procedures")
tbl = table.open("d04_05")
query.filter = "D04_04"
query.order  = "D04_04"
query.options = "T"
tbl.fetch_first()
while .not. tbl.fetch_eof()
fil.write_line(" <D04_04_0 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">") 
fil.write_line("      <D04_04>"+alltrim(tbl.D04_04)+"</D04_04>")
fil.write_line("      <D04_05 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.D04_05)+"</D04_05>")
fil.write_line(" </D04_04_0>")
tbl.fetch_next()
end while
tbl.close()

Here is what the xml should look like..

Code:

 
<D04_04_0 Status="A" Date="1967-08-13">
   <D04_04>89.820</D04_04>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
  </D04_04_0>
  <D04_04_0 Status="A" Date="1967-08-13">
   <D04_04>89.820</D04_04>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
  </D04_04_0>

I dont have a problem creating the xml just the filtering based on the Fields. As you can see the Field D04_05 is based on D04_04. The tables are linked.. I cannot figure out how to fetch the child data based on the parent.. In other words.. the parent field is D04_04 and the child is D04_05...

**Stan Mathews** · 06-27-2009, 12:45 PM

Re: Filtering child values

Originally posted by Firemedic934 View Post

Thanks,

I am trying to understand the fetching. I am also trying to open the index on the tables. I am running in to difficulty doing that.

do i use the indx commands or create the index in each table?

When working with a set you can't use any indexes except those defined for the parent table. You don't need to open the indexes, opening the set puts them into play. To choose which of the index tags is in effect, use the

<TBL>.INDEX_PRIMARY_PUT()

method.

query.filter = "D04_04"

"D04_04" is not a filter. Valid filter statements would look, assuming the "D04_04" field is character type, like

query.filter = "D04_04 = 'ABC' "
query.filter = "D04_04 = ' 123456' "

Your code above isn't working with a set. You are opening two tables. You are using the same pointer for both

Code:

[B]tbl[/B] = table.open("procedures")
[B]tbl[/B] = table.open("d04_05")

which means you are overwriting the first with the second, effectively closing the first.

Here's a link to the documentation for fetching through records in a set.

**Firemedic934** · 07-01-2009, 08:54 AM

Re: Filtering child values

Here is the code that i am trying:

Code:

 
tbl = table.open("d04_05")
tbl.index_primary_put("D04_04")
tbl.fetch_find("D04_04")
while tbl.D04_05 = "D04_04" .and. .not. tbl.fetch_eof()
fil.write_line(" <D04_04_0 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">") 
fil.write_line("      <D04_04>"+alltrim(tbl.D04_04)+"</D04_04>")
fil.write_line("      <D04_05 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.D04_05)+"</D04_05>")
fil.write_line(" </D04_04_0>")
tbl.fetch_next()
end while

this still does not work.

I have read the fetching thing, and i am having difficulty trying to figure it out.

**Stan Mathews** · 07-01-2009, 09:14 AM

Re: Filtering child values

this still does not work.

We need to know what this means.
Not work isn't very informative. Are you getting errors?

Code:

tbl = table.open("d04_05") [COLOR="Red"]'opens the table d04_05[/COLOR]
tbl.index_primary_put("D04_04") [COLOR="red"]'sets the index  to the tag D04_04[/COLOR]
tbl.fetch_find("D04_04") [COLOR="red"]'finds(tries to find) [COLOR="RoyalBlue"]the value "D04_04"[/COLOR] in the index
'but doesn't verify that it is found[/COLOR]
while tbl.D04_05 = "D04_04" .and. .not. tbl.fetch_eof()
fil.write_line(" <D04_04_0 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">") 
fil.write_line("      <D04_04>"+alltrim(tbl.D04_04)+"</D04_04>")
fil.write_line("      <D04_05 Status =\"A\"" " Date="+time("\"yyyy-MM-dd\"")+">"+alltrim(tbl.D04_05)+"</D04_05>")
fil.write_line(" </D04_04_0>")
tbl.fetch_next()
end while

After setting the index to D04_04 do you mean to find some value? Normally one would have an index tag like NAME and then find "Bill", "Joe", or "Bob".

Time to attach a sample so we can see what your input is for this script.

**Firemedic934** · 07-01-2009, 09:27 AM

Re: Filtering child values

ok, i have uploaded the database.

you can see the tables..

I have a set named Procedures, in the set is the table D04_05

I have linked the set with the D04_04 field. This is the code for the procedure... Ie. CPR 100 The other table is the personnel permitted to use the procedure. field name is D04_05. the D04_05 field is filtered by the value of D04_04..

XML CODE

Code:

 
<D04_04_0 Status="A" Date="1967-08-13">
   <D04_04>89.820</D04_04>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
   <D04_05 Status="A" Date="1967-08-13">String</D04_05>
  </D04_04_0>

this is what the xml should look it. the procedure code is D04_04 and the personnel permitted to use it are linked to the procedure code. Thats why there are 2 D04_05 listed there.. I dont have a problem making the xml, just the filting of the data in the set.

I am thankful that there are a lot of experts out there that can help me. It is very much appreciated.

**Stan Mathews** · 07-01-2009, 11:02 AM

Re: Filtering child values

The attached script should get you closer. I made several changes to your table pointers, edited two lines and put comments to the right of them.

We are still left with the issue of what you are trying to do with

Code:

i_tbl = table.open("d04_05")
i_tbl.index_primary_put("D04_04")
i_tbl.fetch_find("D04_04")
WHILE i_tbl.D04_05 = "D04_04" .and. .not. i_tbl.fetch_eof()

The line i_tbl.fetch_find("D04_04") searches for the text "D04_04" in the index tag "D04_04" for the "D04_05" table. Table "D04_05", is indexed on field D04_04 for the tag D04_04. The values in this field are "100" and "101". Searching the index for "D04_04" won't find any records because

"D04_04" is not equal to "100" and it is not equal to "101".

Are you trying to process all (currently 4) records in D04_05 in order of the values in D04_04?

**Stan Mathews** · 07-01-2009, 11:51 AM

Re: Filtering child values

Try explaining it this way.

I want to open the D04_05 table.
I want to put the D04_04 index tag in effect so records are processed in that order.
? I want to find a particular value in the D04_04 field ? or I want to go to the first record in that order ?
Then I want to ......

Announcement

The Alpha Software Forum Participation Guidelines

Filtering child values

Filtering child values

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment