How do you load a specific record?

Applications Forms
#1

I’ve created a login system, where there is going to be companies and customers logging in. When they login, I want to load their specific record in the form, not just the first one. How would I go about doing this? Do I need to write my own code, or do I have to change the “Edition Lookup” for each field, on all the forms?

#2

Re: How do you load a specific record?

Have a look at sc_lookup(). You can SQL any table/data you want.

Scott.

#3

Re: How do you load a specific record?

Right now, the person logs in and then it loads a form. In that form, they can go through each record, and it loads the first one in that table. What I want, is for it to just load the record associated with their login, which each table has a login_id field that corresponds to their login. Is sc_lookup the function I should use for that?

#4

Re: How do you load a specific record?

Sounds like you need to filter the record in the form after the login. I was thinking you wanted to locate a single record after login.
sc_lookup is not the solution.

In the form to load, access the SQL property (Where Condition) and add:

login_id = [global_login_id]

This will filter the form based on the id_login

Now to create the global login id, place this in login form (onValidate):


sc_lookup(db_users,"SELECT login_id FROM users WHERE user_name = '{input_username}' AND password = '{input_password}'");

if ({db_users} == false || empty({db_users}) ) { 
  sc_error_message('Invalid login detected!');
 } else {
  [global_login_id] = {db_users[0][0]};
}

Regards,
Scott.,

#5

Re: How do you load a specific record?

Thanks alot for your help, but how do I access the SQL property(WHERE condition), is there a function or is it a setting in ScriptCase? Right now I have the login_id being passed to each form that needs it.

#6

Re: How do you load a specific record?

Alright, I think I got it to work, my next question would be, how do you do this dynamically? I have a ‘name’ field, and I would like it so that when someone types in a few letters, it would search for that person’s record(s), and then display those in the form? Is there a function to change the forms SQL WHERE clause?

#7

Re: How do you load a specific record?

SQL (Where Condition) is a property editor in the form app. Click SQL and you will see Where Condition editor. Just place the condition and not the WHERE predicate in the clause.

I am a bit confused about your last post. Are you creating a multi-record form, or a grid, or? You mentioned record(s)… plural

If you are display the record(s) in a grid, then that would be easier as you could check for an active SQL statement in the onInit of the grid and then update the SQL statement before that call with something like:


  $sqlprefix = (empty({sc_where_current}))?'WHERE':' AND'; 
  $sqladd = ' (id_request_user = '.$_SESSION['g_iduser'].')';

  sc_select_where(add) = $sqlprefix.$sqladd;

Regards,
Scott.

#8

Re: How do you load a specific record?

I am using a single record form, some companies will have 1 record while others might have multiple records. I also have an “admin” user who should be able to go through all the records. What I would like to be able to do is, if it’s just a regular user, have the select statement be WHERE login_id = [glo_login_id], and if the user is an admin, I just want it to have no WHERE clause so all the records are there, and they can click the next/last buttons to go through each record.

#9

Re: How do you load a specific record?

just for your information, i figured it out. This is what I did, in the onInit event, I check the user’s login level, and depending on their level i set a variable called $sql to either a blank string or "login_id = " . [login_id], and then I set it as a global, then in the SQL settings I put the [sql] variable, and it worked.