how to create a form taking data from multiple tables instead of 1?

Applications Forms
, , , ,
#1

hi,

i have two tables. one is called campus_profile which has the campus_id and user_id. this shows which user is allowed to access which campus.

i also have another table which is called campus_master. this tables hold the name of the campus for each campus_id.

my question is,when i create a form and display it from campus profile it shows which user is assigned to which campus. but since this table only have campus_id, there’s no name to show at a field. it’s not appropriate to show the campus_key since it’s not giving any meaning to user.

so i can link it by query which is by linking the campus_id between these tables.

but from what im seeing, forms only allow reading from one table. how do i add another field and the data would be coming from my campus_master table. i can create a view, but it wont be easily to update. i need to update since i want to allow or restrict user from a certain campus access.

any ideas?

#2

I don’t quite understand what you want to achieve. You can have an autolookup on any field replacing the keyvalue by the description. You can use another table here. So you don’t need a link here to replace the capus id by the campus description. Is that what you mean? Don’t change the field type (text, number), but go down to grid lookupsection. Click the checkbox ‘Use lookup to display the field description.’ and generate the lookup SQL Command. Is that what you are looking after?

#3

Thanks Albert for your reply,

perhaps my situation isn’t clear yet or i’m still struggling with the tools. i did try the grid lookup section but it doesnt show as value but rather as description and an empty box beside it.

let me give you a simple example of my situation.(im using oracle database):

i have two tables which is campus_master and campus_description…

table structure as follows:

campus_description

USER_KEY | USER | CAMPUS_KEY | ACCESS_FLAG |

221 | jack | 7 | Y
206 | scott | 5 | Y
200 | james | 6 | Y
221 | jack | 6 | N

campus_master

CAMPUS_KEY | CAMPUS_NAME

5 | SD01
6 | SD02
7 | SD03

now,im creating a form using the campus description to show user which access flag they are allowed from each campus. but from what you can see, there is no name for campus in that table. there is another table which holds the campus names by their key at the campus_master table.

now i’m going to need to show campus_name on the forms because showing campus_key would be pointless to user.

so in theory, im gonna have to link by query using the campus_key between the tables to get the name. i was asking how to do that for a new field?

i did try the grid lookup section by inserting this sql:

select a.campus from campus_master a, user_profile_campus73
where a.campus_key = '{CAMPUS_KEY}'

it did show up correct,but beside an empty box. it shows up as description.

snip.PNG

#4

Afaik: Create a form using campus description. Open fields in your left menu and select campus key. Go down to the grid lookup and check use lookup. Click create select. Select correct table name (campus-master). Field that will be shown is campus name. Click ok marker and save form. Now run it. It will show the description of the field behind the keyfield. If you don’t have too much campus description and you don’t want to show the keyfield with description you can change the fieldtype to ‘select’ type and follow similar steps to add the appropiate sql. Then the description will appear in the field (as a pulldown) and you don’t see the keyvalue. If this form is read-only just check the property ‘label field’.

You don’t need to use the formfield in your sql statement to select the correct value as this is done for you. Besides that, the sql statement is wrong. You can try this on the sqlbuilder to see the result.

Does this help?

#5

[QUOTE=aducom;11362]Afaik: Create a form using campus description. Open fields in your left menu and select campus key. Go down to the grid lookup and check use lookup. Click create select. Select correct table name (campus-master). Field that will be shown is campus name. Click ok marker and save form. Now run it. It will show the description of the field behind the keyfield. If you don’t have too much campus description and you don’t want to show the keyfield with description you can change the fieldtype to ‘select’ type and follow similar steps to add the appropiate sql. Then the description will appear in the field (as a pulldown) and you don’t see the keyvalue. If this form is read-only just check the property ‘label field’.

You don’t need to use the formfield in your sql statement to select the correct value as this is done for you. Besides that, the sql statement is wrong. You can try this on the sqlbuilder to see the result.

Does this help?[/QUOTE]

Thanks Albert!
That did help me but not all the way. Appreciate if you could help me a little bit more. You said that if i select fieldtype to ‘select’ and the keyfield is not shown,only the description. That’s nice,no key is shown.

how about i want to remain it as it is(i dont want to use ‘select’) but only show the description? reason is because this is just for viewing or should i say read only. if i use select it will be as though i can change the campus.

for now, i also tried setting the ‘label field’ to yes to not allow it for edit. but i dont want to show the keyfield.

now what i got is like this:

How do i show the description only?is it possible?

snip.PNG

#6

Hello psychocryo, you may change your campus_key field type with text-autocomplete or number-autocomplete and control the way your field description with automatic sql.
and choose label only after you changed that.
It Works for me.