Data in agileBase - agileChilli
Smart fields – automatic generation of field values
This feature lets you automatically pre-populate field values for new records, using calculations or lookups.
Here are a couple of uses that trial customers have already found:
- For a purchase order app, when entering a new purchase order, the system looks up the previous price and quantity purchased and pre-populates them. This makes repeat orders really easy and also lets the purchaser see whether the item’s getting cheaper or more expensive.
- In a centralised system for a large group of companies, with central oversight by the common owner, invoice numbers and job numbers need to be given their own unique IDs, however they also must be sequential within a company. So for example, company A might have numbers A0001, A0002, A0003 etc. and B may have B0001, B0002, B0003.
A smart field lets us look up the company the logged in user belongs to, find the sequence for that company and create a new number with the relevant prefix.
How does it work?
We build on a feature that already exists – referenced fields. In brief, a referenced field pulls in one or more fields or calculations from a view into a table form. The view can be any view in the system, as long as it contains the ID field from the table it’s in, so it can select the right record.
We simply use the value pulled in to populate a standard field.
Once you have a referenced field, drag it up to just above the field you want to populate. Then in the options for the field you want to populate, select ‘Set from previous referenced field’.
When the user creates a new record, the value from the referenced field will automatically populate the field under it. The referenced field itself will become invisible.
To auto-populate a relation field, there’s a slight difference. In the view for the referenced field, add in a field or calculation to display the value to the user. This should have the same name as the ‘display value’ option of the relation field, e.g. if you’re relating to a company it may be ‘company name’. Then after that, add in a calculation for the internal row ID of the record which should be attached.
‘Digital Signature’ stamps
Sometimes in compliance environments, your software needs to track changes to fields by individual people, particularly sign-offs.
agileBase always keeps a complete history of user changes behind the scenes anyway, accessible to system administrators, but now we’ve introduced a way of making sign-off actions visible on the user’s screen, so everyone can see quickly when things were done and by who.
Next to any checkbox, there’s an option to display the date of the last change, along with the name of the user who made it. To turn this on, just go to the field options as normal.
Google docs integration
Agilebase users often attach documents to their data – quotes, invoices, process maps, certificates etc. The built in version control keeps a history of any previous versions when a new file is uploaded to the cloud, allowing rollbacks if necessary. Great for documents of any type regardless of their origin.
However when drafting your own documents, Agilebase (until now) required the user to descend from cloudy heights to the swampy lowlands of local storage. In practice this often meant that two people could be simultaneously drafting a document but having to work on different copies of the file on their respective computers, each having no knowledge of the other until an upload was done. Cue loads of uploading and downloading of files!
Google docs offers a much better way to work collaboratively for those who want to take the 100% cloud route. A central copy of the file is updated in real time, and all users can see changes immediately, even if a few people are working on a document at once.
Agilebase now comes with Google docs integration! This means you can get the advantages of both ‘cloud’ systems without the need to go offline to make changes. Link directly to a Google document, drawing, spreadsheet or presentation from within Agilebase and the system will
a) show you a preview of the doc, which expands as you hover over it to let you read the contents
b) show information such as who last made an edit and whether there have been any edits since you last opened it
The easiest way to do this is to simply copy and paste the URL of a Google Doc from your browser’s address bar into any text field in agileBase.
If more than a couple of people need access, linking docs from the Agilebase account offers a much easier way of sharing than just usingitself. As well as attaching meta-data to the document, which of course you can do easily with Agilebase, you can skip the laborious process of sharing and inviting everyone through Google (the only real crinkle in Google’s otherwise smooth processes). People can just click the link and if they don’t yet have access, an access request will be sent.
So in summary, take the cloud route and get the benefits not just of your core databases in the cloud, not just your documents, but the two together, linked and referenced for easy collaboration and management.
Our latest client’s business revolves around a database of TV programmes, for which they want to attach data such as stars, narrators, channels and countries shown in. For this type of data a dropdown selector doesn’t really cut it because there may be more than one value needed – a programme will have more than one star and may be broadcast in more than one country for example. Using new tables for each of these would be a bit too cumbersome.
Together, we came up with the idea of using tags – similar to LinkedIn endorsements, youtube video categories and many other systems. Want to add a new country? Just type it in and add it as a tag.
In our own business, we’ve started using this for job roles – as one contact may have more than one role. Here’s a screenshot of a contact with two roles:
As you type a new tag, the system auto-completes from tags already in the system.
Do you see any use for this in your own organisation? To set it up, just go to the field options and tick ‘use tags’.
Assigning users and groups to records
In brief, the system can assign users and groups to individual data records anywhere in the system. So for example, if you want to take your sales pipeline, to do list, inventory management system or anything else, and assign individual entries to individual people, you can do so easily.
This is only one step along the way to another facility we’re building at the moment, a comprehensive notification system, coming soon. But we think it’s an exciting feature in it’s own right.
Any text field that’s configured as a dropdown list or tags field, can be set to get it’s contents from the user list or group list. To set this up, go into the administration interface, select a table (under Build), click the fields tab and click ‘edit options’. You’ll see a dropdown from which you can select
- users and groups
These are the types of objects which people be able to select when they’re entering data.
It’s very flexible. Since it works through any dropdown or tags field, you can assign either one or multiple users or groups to each record.
What’s more, when editing a record, only the users/groups who have privileges to see that data will appear. That makes data entry a lot easier – if there are many users in the system only the relevant ones pop up.
As mentioned, this will feed into a notification system so staff get notified of items relevant to them, but even before that there are lots of uses you can put it to, e.g.
- assigning staff to items without having to use a separate ‘staff’ table. This saves wasteful duplication and works well if not all users are internal staff.
- in collaboration with fine grained access control, allow each staff to see lists of their own items, without needing to set up a separate view per user
Finally, even though the system will present lists of users and groups for selection, data entry isn’t necessarily limited to these. If the administrator doesn’t tick the ‘use only given values’ field option, people will still be able to add other values themselves. So for example if you want to mark a task as assigned to a one-off contractor who’s not set up as a user, that’s no problem.
We think all of these things make this a very powerful and flexible feature. We look forward to seeing how it’s used.
Auto-filtering relation data
This one’s an advanced feature that will only be used in certain circumstances but is very beneficial where there is a good use. It’s most easily explained by an example.
Here, we’re adding a product to a customer order. There’s a relation from order line to product that lets you select from the full range of products. However, what if the product list depends on the customer? Different customers may have different products that they’re allowed to order, i.e. an agreed product list. Using this feature we can set that up.
The idea is that the product list is filtered by a value from the order. To specify the filter value, add a cross-referenced field to the main order record. In this case, a cross referenced field is added to show an ‘order list’ name. After adding the cross referenced field, edit it’s options and tick ‘use as a filter for relations’.
Each product is assigned to one or more order lists and the order list field added to the view used for selecting products.
Then whenever an order is added, the value supplied by the cross-referenced field is used as a filter to the product list, to narrow down the products.
This type of filtering can be used whenever there is a parent-child type relationship where the child options need to be narrowed down depending on criteria from the parent.
Note: the view used for choosing items will be filtered on any field with the same name as one used in the cross reference. That means it doesn’t need to be exactly the same field, the cross reference can be e.g. a calculation.