Submission form to search multiple tables

[JaguaR]

Hello,

I have the attached table structure. I want to make a submission page to BilledHours table. The flow of submission should be like the following:

- user authenticates to submit billing hours;

- after authentication, based on UserID I want to auto fill the First Name and the Last Name from Users table;

- also after authentication I want to just list the ProjectsNames from Projects table, the WorkName from WorkType table;

- it would be nice to also auto fill RoleName from Roles table based on RoleID from the Users table, that will be limited by UserID, but this is not compulsory.

- then user just enters the start date and the end date, selects the project and work type, inputs the number of hours and submits;

- on submit in BilledHours we will have only the IDs like WorkID, ProjectID and UserID passed from the auto fill options.

Question is, can this be done in Caspio? I tried multiple things from Data Pages and can't find an approach that would satisfy the above.

Any help or suggestion to change this structure is appreciated, but it is possible to build this using normal SQL.

Thank you!

[MayMusic]

As I understand you have couple tables that will be used as look up tables such as Projects, Work Type and Roles. You can create an authenticated DataPage based on BilledHours table which has the users table as authentication table. Then in the DataPage wizard you can set the form element of the UserID to be hidden this way you can select the authenticated field from the authentication table.

Also for ProjectID and WorkID you can select dropdown as Form element and select the appropriate table as the table source, the field for display and for value is also selectable.

Please look at the screenshot from the DataPage wizard, DataPage after design and the data saved to the table.

[JaguaR]

@MayMusic,

Thank you for the reply, the dropdown thing for projects and worktype is simple indeed. The problem here arises how to auto-complete the first name and the last name and then pass the userdID after inserting values?

Thank you for your help!

[JaguaR]

For other who are looking at this topic, I think I found the way to do this, you can place the values from the authentication table in HTML blocks like this:

[@authfield:First_Name] [@authfield:Last_Name]

Would be nice to know all of these available parameters to the developer. I've seen there are a number of [@cb] parameters, it would be very useful to see a full list of them.

[MayMusic]

Thanks for sharing. These parameters are also available:

[@cbHostURL] gets the full URL

[@cbHostDomain]

[@cbHostPage] part pf HTTP_REFERER URL srting, obtained server side. Contains page address. Everything that preceded first ? (question mark) in the referer URL

[@cbTimestamp]