[SOLVED] How Can I do this, Set a variable based on value in table

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9517019096

November 07, 2018, 09:40 AM

RobertF

[SOLVED] How Can I do this, Set a variable based on value in table

I am confusing myself but I think I should be able to do this.

I want to evaluate a table and see what the LATEST month is in the table.

I want to set a variable equal that value.

I want to test the variable to controll which fex to run.

Example, if the latest period in the financials table is 201809 then I want to run fex1 otherwise fex2.

I have done similar things by prompting the user for, say a report type, and then evaluating what they chose but can I set a variable to the max period in my financials table?

I hope this makes sense.

Perhaps someone can share code using CAR to show me how?

This message has been edited. Last edited by: FP Mod Chuck,

November 07, 2018, 09:42 AM

BabakNYC

Run the first request to find the date you want and HOLD then -READFILE the field from the HOLD file.

Here's an example I found by another user using search:

  
TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
BODYTYPE
SEATS
DEALER_COST
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS HLD_VARS
END
? HOLD HLD_VARS
-RUN
-READFILE HLD_VARS
-TYPE --------------------------------
-TYPE COUNTRY --------- &COUNTRY
-TYPE CAR ------------- &CAR
-TYPE MODEL ----------- &MODEL
-TYPE BODYTYPE -------- &BODYTYPE
-TYPE SEATS ----------- &SEATS
-TYPE DEALER_COST ----- &DEALER_COST

This message has been edited. Last edited by: BabakNYC,

November 07, 2018, 09:55 AM

RobertF

yes, I do not know how to do that? I know I need a variable to house it.

November 07, 2018, 09:56 AM

BabakNYC

I just posted an example above. Notice the &variable name is assigned using the hold file's column name.

November 07, 2018, 10:03 AM

Brandon Andrathy

Hello,

Here is another similar solution that I use to accomplish this:

 
TABLE FILE CAR
SUM
	DEALER_COST
WHERE COUNTRY EQ 'America';
ON TABLE HOLD AS 'HOLD' FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-READ HOLD &DEALER_COST.A8.
-TYPE &DEALER_COST

BabakNYC's code looks a lot more efficient for multiple variables. I'm gonna use that moving forward!

November 07, 2018, 10:13 AM

RobertF

well this works EXCEPT it prompts for a value for &PD...I can not have this prompting though...thoughts..

SET ASNAMES = ON
TABLE FILE TBLMSTR_YTDEMPLOYEEFTES
BY LOWEST TBLMSTR_YTDEMPLOYEEFTES.TBLMSTR_YTDEMPLOYEEFTES.ASOFPERIOD AS PD
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDPD FORMAT ALPHA
END
-RUN
-READFILE HOLDPD
-TYPE LATESTFTEPD IS : &PD

I believe I should be able to evaluate LATESTPD...but I can not have the user prompted...

November 07, 2018, 10:15 AM

Brandon Andrathy

Hey Robert,

I believe you need to have the value you want to read as either a SUM or a PRINT in order for it to be read.

November 07, 2018, 10:17 AM

FP Mod Chuck

Robert

Use


-DEFAULTH &PD='';

at the beginning of the fex.

November 07, 2018, 10:19 AM

RobertF

it is reading my table and printing that latest Period when the type statement is executed. It works. It is also prompting for &PD...which is irrelevent since whatever I enter at the prompt is replaced with the data from the hold file created. How do I get rid of the prompt? It makes no sense to prompt the user.....

November 07, 2018, 10:23 AM

MartinY

SET ASNAMES = ON
-DEFAULTH &PD = ''
TABLE FILE TBLMSTR_YTDEMPLOYEEFTES
BY LOWEST TBLMSTR_YTDEMPLOYEEFTES.TBLMSTR_YTDEMPLOYEEFTES.ASOFPERIOD AS 'PD'
ON TABLE HOLD AS HOLDPD
END
-RUN
-READFILE HOLDPD
-TYPE LATESTFTEPD IS : &PD

November 07, 2018, 10:31 AM

RobertF

THANKS!

I seem to be able to evaluate &PD now...no prompts!

November 07, 2018, 02:56 PM

Waz

Robert,

What happens with parameter prompting is the Fex is scanned to all &vars, and if there is no -DEFAULT(S|H) or -SET (before the first use) then the prompt will happen.

This can be turned off in the Administration Console.

As an extra note, two suggestions were given to you to stop the prompt.

-DEFAULTH, this provides a default value of the variable.
-SET, this sets the variable to a value.

An extra option is to use the -SET with no value.

e.g.
-SET &PD =;

This clears the variable, and also stops the prompt.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!