How to descibe Excel Mileage Chart table

[Craig]

Kind of a weird one but here goes.

We have an Excel spreadsheet that contains a table to display mileage values
between sites. Each column in the top row has a name for a site and the first
column of each row has a name of a site. The names across and down are the
same so the cell where the same site intersects itself has an "X" since there
is no mileage value departing and arriving at the same place. Using this
table we can figure out how far we traveled by scanning across the top row
for a site we departed from, scanning down the first column for a site we
arrived at, and then using the value in the cell where the two sites
intersect each other as the mileage.

My question is, how do I descibe this data in a way I can use it in
Infopath. I have a drop down box for departing and a drop down box for
arriving and a text box for the mileage value betwixt the two sites. I want
to populate the fields of each dropdown box to correspond to a site from the
row or column and use the value as the row or column identifer. Then I want
text box to use the values from the two drop down boxes to find where the row
and coumnn intersects and use the value of that cell as the value of the text
box.

If I were writing a program I would have a two-dimensional array recursively
search the first row and first column to get an index value of the one which
matches the site, and then use the index values as a coordinate point in the
array to pull the value I need.

I have the data, I just don't know how to descibe it since it is not your
typical column/recordset table.

Does this make sense? Imagine a mileage chart like this:
http://www.hm-usa.com/distance/usa.html

Any help would be appreciated.

Thanks

 

[S.Y.M. Wong-A-Ton]

Where do you have the data stored? In an XML file? In a database?

You could create an XML file containing both the names + identifiers for the
sites and the distances between the sites, and use this as a secondary data
source. Your XML data source could look something like:

<lookuptable>
<sites>
<site>
<id>1</id>
<name>Site A</name>
</site>
<site>
<id>2</id>
<name>Site B</name>
</site>
<site>
<id>3</id>
<name>Site C</name>
</site>
.....
</sites>
<distances>
<distance>
<site1>1</site1>
<site2>3</site2>
<miles>20</miles>
</distance>
......
</distances>
</lookuptable>

You only need to specify a site1/site2 combination once. For example, the
distance between Site A and Site C is 20 miles, but the distance between Site
C and Site A is also 20 miles, so you would only specify the Site A and Site
C combination once in a <distance> node under <distances> node.

You can then pull the values for the two drop down list boxes on your form
from the <sites> section of your XML data source. Both drop down list boxes
would display the same sites.

Then you have to add a rule to each drop down with an action that sets the
value of the text box to the amount of miles between the two sites selected
in the first and second drop down list boxes. The miles data will be pulled
from the <distances> section in your XML data source.

You also need to add a filter condition when you are retrieving the data for
the amount of miles. The filter condition must say: The id of the site
selected in the first drop down list box is equal to the id in the <site1>
node AND the id of the site selected in the second drop down list box is
equal to the id in the <site2> node OR the id of the site selected in the
first drop down list box is equal to the id in the <site2> node AND the id of
the site selected in the second drop down list box is equal to the id in the
<site1> node.

That should do it.

 

[Deborah Jean]

every thing has to be within those little < > signs. Like Access has to have
the [ ] I just got info space and we are going to be teaching 2003 any thing
I can teach them about intergration of the programs is great. That's why I'm
snooping on all your answers. Dang, you're smarter than the average bear.
Thanks for letting me snoop around your answers.

Deborah Jean