Setting up DBF sets: Editing link expressions problem when a variable is needed....

[SNusa]

Trying to use a variable in the link expression of a set (dbf).... Something appears to be wrong here.

First, I can't use any of the variables even if they are both "assigned" in the table, and also in the set definition.
I've tried vc_VariableName, and also var->vc_VariableName. Looks OK, but either way I get the error "Field is not recognized" when I attempt to save. (Hence, I can't save the expression!)

Second: When I'm editing the link expression and I press the + key..... I get a list the table fields. At the top of the list I see a "Calc->" entry.
Besides the oddity that the "Calc->" entry exists but not a "Var->" entry..... Even when I try to assign a global variable via a calculated field, I encounter the same problem as above.

I've already tried running the app to insure the global variable is set. It doesn't make a difference. I've also found that when trying to enter characters into a link expression, they don't always appear as they should.....

Ironically you can use variables (and even functions, there's a button for add a function in the link definition) when setting up other aspects of the set..... But variables are apparently not working properly in this one particular spot. ~ I really need to be able to do this. Any suggestions on embedding a variable or function within the link definition? I'm working with a Global variable which is set via script when the application is initialized.

~Going to try and create a global function. NOPE.... That won't take either. FWIW: This value is only assigned once when program initially launches.
(Returns a similar error indicating my function is not recognized.)

All I'm trying to do is assign the parent link expression side to: "MASTERKEY + vc_GlobalVar" (where MASTERKEY is the parent field, and vc_GlobalVar = "000001")
"Hardcoding" the following link expression: MASTERKEY + "000001" works fine. Just can't define via variable/function/calculated value.......


Function:

'Date Created: 17-Feb-2016 05:57:24 PM
'Last Updated: 17-Feb-2016 05:59:12 PM
'Created By  : Business
'Updated By  : Business
FUNCTION MerchantSuffix_fnc AS C ( )
	dim global vc_Suffix as C 'global variable initialized via autoexec script
	vc_Suffix = lookupc("C","MERCHLINK","parmfield0","setup_cfg","Prmd__")
	select case
		case vc_Suffix = "00000" 'suffix not needed
			vc_Suffix = ""
		case else
			vc_Suffix = "_" + alltrim(vc_Suffix) 'append suffix
		end select
MerchantSuffix_fnc = alltrim(vc_Suffix)
END FUNCTION

'==========================================================
ui_msg_box("MerchantID",MerchantSuffix_fnc())
end

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

One other thought here: I am able to use pre-defined functions, but what's the point if you can't use variables with them either?
Example: dtoc(date()) and similar pre-defined functions work just fine. (So long as there are no variables used?)

[Stan Mathews]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

I don't believe you can use a variable because the linking key for a set requires Alpha to build an index. Using a variable would require the index and the links to be fluid. Or at least the set builder seems to think that's what you want.

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

Hi Stan;
Could have sworn this was possible in previous versions, likely V.10x (where I started this crazy app?) (It was part of my "plan" way back, but I needed to take care of 10,000 other things before adding back this flexibility.) I'm going to check. I still have v.10.5 installed on a prior version of windows residing on this system. Even so, since you can use built in functions (like string functions with field values and fixed numbers) seems odd..... Irrespective, in this scenario, fluidity is not what I am trying to accomplish. Value is set before set is used by layout. It's a "set link and forget" for each run of a5. ~ Might be able to accomplish the same thing with the right string functions etc. I may ask alpha about this.

Question on the index building: The index you're referring to.... When do you think these set dependent indexes are built? On set design, or on set usage? Also, what is the reasoning for providing the Calc field (and the variable definition) capabilities within sets? I can see the reasoning for this on tables so you have table field default values (to avoid table corruption while working with the tables alone)..... But on sets if they can't be used, why does the variable and calc value assignment capability exist?

[Stan Mathews]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

I think when designing, to prepare for data entry. You can link on calc fields to allow multi field links. You can create set variables so that they are available whenever the set is used.

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

Thanks Stan...
Question: Are you implying that it would not be a good idea (even if I'm successful) to use string functions (in the linking field definition) to conditionally strip away the suffix portion of the link? ie: "_000001" (Or optionally use a conditional calculated value as the link, and reference the global variable that way? (global variable assigned once, only at startup)

(And which method would you suggest, if any?)

FWIW, I did learn that if you have field rule (defaults) for a table: That removing a variable (upon which a field default is calculated) will corrupt (remove/strip) all of the indexes from the table. If you make this mistake (as I did lol): You're left with a table with all the user defined indexes missing/gone forever.

SOLUTION/WORKAROUND?
Not sure about performance issues of doing it this way (given the index setup thing you explained) but it seems fine. (feedback certainly welcome).....
I was able to create the following child link definition (one simple character extraction function) which accomplishes what I need while avoiding usage of the global variable: word(Masterkey,1,"_",1) Note: I might add an alltrim(), just for safety sake!

[DaveM]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

Sets are "best" built with table fields. No mods if possible. Those table fields should be indexed.

I almost never combine fields in a set like xyz+pet.

other
dim t as c
t = dtoc(mydate) will work fine where mydate is a field
if var then
t = dtoc(var->mydate) will work
Use these in scripts, functions and other where accptible

Dates are generally NOT a good linking field

Suggest not trying to do a set as any type filter. Too many other ways to get there(if that is what you are trying)

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

I totally agree Dave. I do very little with the table and field settings themselves. (and usually NOTHING with the sets) Most everything (validation, etc.) is done in the layout etc. (with the exception of uniqueness sometimes, setting indexes) I avoid using auto-increment as well (mostly for multi-user performance/record locking considerations.)

The only reason I am working with modifying the linking field in this set is:
Upgrading to multi-merchant updates the linking fields. (all the sub tables) It appends the fields with an underscore and a "text number': aka: "_000001, _000002 etc." By default (before the upgrade) Without going "multi-merchant" (for future web/tablet integration) this "suffix" does not exis. Once upgraded to multi-merchant (changing the merchant id from "000000" to something else in a setup file field: I want to keep the customers all tied into multiple merchant data. (as opposed to having multiple customers ID's, one for each merchant)

I suppose I could restructure by adding/using a different linking field. (Probably a smart thing as I eventually anticipate the web side integrating this data in mySQL/mySL fork like MariaDB.

On a side note: Why can't I get this to go away (even after updates) in news and updates?:

You do not have the most recent release of Alpha Anywhere.
Click here to download and update to build 2999.

When I click on the subscription info link in a5, everything is OK: (It correctly indicates the subscription expires next year.)

[DaveM]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

I am going to mention the long field names that many use, but not most.
My field names are max of 10 characters.
Just hope you luck on those.

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

I am going to mention the long field names that many use, but not most.
My field names are max of 10 characters.
Just hope you luck on those.

I'm referring to the data within the field with the linking suffix "_00001" etc..... Not the field names. All my field names are 10 MAX, indexes are 8 max (2 underscores for #7 and 8) so I know they're mine even if one char is somehow "cut" from the index name. (Never seen it happen, only read about the possibility.) ~ One trick I have used to keep these index names short, is to use the field names while stripping all the vowels and then adding the two underscords. Keeps things logical & EASILY recognizable in the code. Almost always fits within the 8 chars without variation to my "vowel-free index naming convention." (And only if the first letter is a vowel in the field do I use it.)

Spent a ton of time trying to learn to live within the safety zone while using a5. All variables are localized as low as possible. (3 globals for the entire application which are absolutely necessary) Pretty much all the code I write is encapsulated within functions, not scripts etc. etc. etc. (With regards to everything I do in a5, I strive to live within the guidelines of CSDA1. If you're using a5, the info on Ira's site is worth it's weight in gold imo!)

PS Dave: Thank you! You just enlightened me as to how I am trying to use a5 to "obscure the lines" between field linking and field data for this set. Definitely not a normal practice. (And how a5 is so forgiving within the way it works with sets build on dbf's.) ~ Going to do this the right way. Add another field to this particular set. Pretty sure that everything else in the app is/was built to accommodate this relatively easy mod.

[SNusa]

Re: Setting up DBF sets: Editing link expressions problem when a variable is needed....

Well, even though using word(Masterkey,1,"_",1) works flawlessly within the set to define the linking...... (which kind of blends the distinction between table fields as links with table data lol)
Still going to do it the right way for two reasons:

1.) SQL variant to be used when data uploaded to a server for multi-merchant.
2.) Also for a "common link" for all merchants (working autonomously) so customers can easily connect to all merchants via the same User Id.

Other than a SSN (which obviously isn't a smart/good idea), anyone have any suggestions other than an email address for global uniqueness? I'm looking for something that is easy to remember/use repeatedly by people at multiple merchants/locations. ~ Something that doesn't typically change (but can be changed if necessary by customer) which ties their ONE account to multiple merchants. (Phone number and lastname+ schemas are a "no-go" due to marriage, phone changes etc.) Looking for something persistent, unique and easy that customer will remember to use...