Data sources and lookups

A form can fill fields with another system's data: you type a person's number and their name, position and area show up. For that an admin connects the system as a data source and defines the lookups forms may ask it. Both are managed in Settings.

Data sources

A source is a SQL Server database or a web API.

SQL Server: server, database and how to sign in: with a SQL Server username and password, or as the account SelfForm runs under. The second only works where SelfForm runs on a domain server, under an account that can read the database.
Web API: the base URL and the headers sent with every call, a token or an API key say. Each header has a name and a value; if the API asks for a token, the name is usually Authorization and the value Bearer followed by the token. Automations can send it data too.

Passwords and header values are kept encrypted and never shown again. Editing a source, leave them empty to keep the saved ones. Check connection says whether the source answers and, if not, why.

A SQL Server source must sign in with an account that can only read: the db_datareader role on its database and nothing else, no server roles, no right to run procedures and no reach into linked servers. SelfForm checks every query, but what truly keeps it from doing more is the account. An API automations send to, on the other hand, needs to take what they send. Whoever builds an automation only sees the web APIs' names, never their address nor headers.

A web API can never point at SelfForm's own server (localhost, 127.0.0.1) nor at addresses such as 169.254.169.254, which in the cloud hold the server's own data; not through a name leading there either. Addresses on your internal network can be used.

Lookups

A lookup is a set question to a source, with a name. Whoever builds a form only sees what each takes and the columns it answers with, never its query.

On SQL Server it's a query starting with SELECT or WITH. Its parameters take an @, as in @number, and are always sent as values, never pasted into the text. It can only read: outside quotes it takes no second query nor words such as INSERT, UPDATE, DELETE, EXEC or INTO, and nothing it changes in the database is kept.
On a web API it's a path under the base URL, with its parameters in braces, as in people/{number}, asked with a GET. When the answer holds the results inside another object, say where, data.items for instance. The API answering that it doesn't exist counts as no results.
Columns it answers with: the name of each column it brings back, one per row; add more with Add another. On an API a column can reach into an object, as in area.name.

An answer brings at most 50 rows. Try the lookup asks for a value for each parameter and shows what it found, or the problem the source gave.

In a form

In the builder, a text, number, email or phone field can look up with one of those lookups:

Value of each parameter: which of the form's fields it comes from, usually the field itself.
Fills: which field each column fills. Text, number, email, phone, long text, date, date and time, select and radio fields can be filled. In a select or radio, the value picks the option with that value or label, in any case; if none matches, nothing is picked.
Lock: beside each column; the field it fills is only filled by the lookup. It can't be typed into, but its value is saved, and it's emptied when the lookup finds nothing.
When it looks up: when the field is left, on the magnifier button right of the field, or both. The builder's preview already shows it.

Entering a record, you type the value and leave the field or press the magnifier. One row found fills the fields; several show a list to choose one from; none says so. Leaving the field looks up again only if the value changed. Filled fields can be corrected before saving.

Only whoever can enter the application's records looks up, and a field they can't see or change is never filled. When the source fails, the form says so without technical details.

Archiving

An archived source or lookup is kept as it was. While it is, forms using it can't run it and say it isn't available, and nothing is sent to it; restored, they work again.

Loading…

Data sources

A source is a SQL Server database or a web API.

SQL Server: server, database and how to sign in: with a SQL Server username and password, or as the account SelfForm runs under. The second only works where SelfForm runs on a domain server, under an account that can read the database.

Web API: the base URL and the headers sent with every call, a token or an API key say. Each header has a name and a value; if the API asks for a token, the name is usually Authorization and the value Bearer followed by the token. Automations can send it data too.

Passwords and header values are kept encrypted and never shown again. Editing a source, leave them empty to keep the saved ones. Check connection says whether the source answers and, if not, why.

Lookups

A lookup is a set question to a source, with a name. Whoever builds a form only sees what each takes and the columns it answers with, never its query.

On SQL Server it's a query starting with SELECT or WITH. Its parameters take an @, as in @number, and are always sent as values, never pasted into the text. It can only read: outside quotes it takes no second query nor words such as INSERT, UPDATE, DELETE, EXEC or INTO, and nothing it changes in the database is kept.

On a web API it's a path under the base URL, with its parameters in braces, as in people/{number}, asked with a GET. When the answer holds the results inside another object, say where, data.items for instance. The API answering that it doesn't exist counts as no results.

Columns it answers with: the name of each column it brings back, one per row; add more with Add another. On an API a column can reach into an object, as in area.name.

An answer brings at most 50 rows. Try the lookup asks for a value for each parameter and shows what it found, or the problem the source gave.

In a form

In the builder, a text, number, email or phone field can look up with one of those lookups:

Value of each parameter: which of the form's fields it comes from, usually the field itself.

Fills: which field each column fills. Text, number, email, phone, long text, date, date and time, select and radio fields can be filled. In a select or radio, the value picks the option with that value or label, in any case; if none matches, nothing is picked.

Lock: beside each column; the field it fills is only filled by the lookup. It can't be typed into, but its value is saved, and it's emptied when the lookup finds nothing.

When it looks up: when the field is left, on the magnifier button right of the field, or both. The builder's preview already shows it.

Only whoever can enter the application's records looks up, and a field they can't see or change is never filled. When the source fails, the form says so without technical details.