Gossamer Forum
Home : Products : DBMan : Customization :

external variable

Quote Reply
external variable
I need a way to create a variable based on an external text file.

To the point:

file.db
col1|col2| col3|
5|red|green
8|gold|chair

ok I need to search this file based on col1 and have results displayed.
Except if I search for 5 and it returns the results I need 5 to be displayed as 'apple'. The catch is that tomorrow if I do the same search I need 5 to be displayed as 'peach'.

File2.db can be the file that defines what the variable is.

1|peach
2|apple
3|grape

And this file is replaced daily so it is up to date.
So the replacement file could be

1|grape
2|apple
3|orange

Of course this format can be different

grape|
orange|
fruit|

Javascript is no go (as far as I can tell)
So what I need is a mod in the file.pl that will look at file2.db and create the variables for file.pl to use. I can't make the change in header fashion nor can I do a lot more table and frame changes.

I hope I haven't gone overboard in my explanation here.

Thanks for any help


Quote Reply
Re: external variable In reply to
First, it would help a lot if you could use the real type of data you're going to be using.

Second, there needs to be something in the second database that relates to the first, either the number or the "color" in the first database must be in the second database. Otherwise there's no way to know which records go together.

Third, the format of both databases must be the same every time.

JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: external variable In reply to
OK,
Real Data -the names have been changed to protect the innocent (and shortened)

file.db

Acct #|F9 Record#|# holdings|Market Value|% of total MV|
9768101|1|10|470187.18|43.5|
9768101|6|6|523240.44|48.41|
9768101|7|3|52960.6|4.9|
4768103|4|9|470447.74|62.42|
4768103|5|1|18071.2|2.4|
4768103|6|6|254804.57|33.81|
4268106|1|4|47539|65.3|

file2.db
F9 Record#|F9NAME|
1|U.S. Govt & Agency Obligations|
2|State, County and Municipals|
3|Other Obligations|
4|Common Stocks|
5|Closely Held Stocks|
6|Mutual Funds|
7|Preferred Stocks|
8|Warrants|
9|Mortgages & Land Contracts|
10|Property Owned|
11|Bank Accounts|
12|Savings and Loan Accounts|
13|Time Deposits - Own Bank|
14|Time Deposits - Other Banks|
15|Demand Deposits - Own Bank|
16|Demand Deposits - Other Banks|
17|Money Market Funds|
18|Other Investments|
19||
20||
21||
22||
23||

As you can see |F9 record#| is constant in both files.
This is a small percentage of the overall workings here but here goes:

Client logs in
display is tailored using html.pl to user
init they will see file.db above
display will show their info using a pre-set search string
initially created in another start.pl which writes the JS and HTML to create the framed logon and display page.
So the user sees their info only
file.db will display the results and the |F9 record#|as shown in file.db
Here I have a link that drills down further
But instead of displaying the numeric value I need it to display the actual meaning.

So instead of displaying (1) it displays (U.S. Govt & Agency Obligations) as the link to drill down on.

This 'meaning' changes daily so what I need to do is open the file2.db everytime file.db is opened and create an array of variables based on file2.db.

%1= U.S. Govt & Agency Obligations
etc. or something similar.

Hope this helps you to help me





Quote Reply
Re: external variable In reply to
Well, that was a little more info than I needed, but I guess it's better to have too much than not enough. Smile

Here's what I would do.

In your .cfg file, add

Code:

open (TRANS, "</full/path/to/file2.db") or &cgierr("error in .cfg. unable to open file2.db.\nReason: $!");
if ($db_use_flock) { flock(TRANS, 1); }
my (@trans) = <TRANS>;
close TRANS;
foreach $tran (@trans) {
chomp $tran;
@temp = split /\|/,$tran;
$translate[$temp[0]] = $temp[1];
}
Then, when you print out your record, use

$translate[$rec{'F9 Record#'}]


JPD
http://www.jpdeni.com/dbman/
Quote Reply
Re: external variable In reply to
I am getting a pipe instead of data in the F9 Record field
with that code. I will work with it to see if I can find the error but if it is obvious to you please let me know.

Quote Reply
Re: external variable In reply to
Ok, I found it
@temp = split /|/,$tran;
needs to be
@temp = split /\|/,$tran;
It works now Thanks a WHOLE lot
I am also working on a drill down for this so that when the records are returned a user can click on the 'Holdings column and it accesses another data file and returns all accounts with the same F9 name. So I may asking for more help if I get stumped.

Thanks again

Quote Reply
Re: external variable In reply to
Thanks for pointing out my typo. I'll fix it in case someone else comes along. Smile

JPD
http://www.jpdeni.com/dbman/