Jump to content


  • Posts

  • Joined

  • Last visited

Everything posted by jgorny

  1. I asked support about similar aggregation issues. Essentially each SQL call in a calculated field is a 1-1 - so you can grab extra an extra row of data but you can't really grab sets based on a single value - UNLESS - you use a FOR XML construct. I am spending some time digging into this aspect of MSSQL because SQL Server 2016 is providing FOR JSON which will allow for similar aggregations but formatted in a way that is easier to work with in javascript than XML. You get a XML recordset. Haven't played around yet with it. The example code they gave me (since I want to build family trees/relationship diagrams with d3) for calc field aggregations is: select convert(nvarchar, date_field, 103) + N' - ' , cast(COUNT(date_field) as nvarchar) + N' | ' from table_name where user_id = [field:idfield] group by date_field for xml path(N'')
  2. Hi, I am trying to work out a way to get a flat view, but having a difficult time trying to figure out how to make Caspio return what I need. I have a table of locations, and a table of workers. Each worker works at one location. Each location has one parent. A location can have many children. Say the location table has: Loc_ID | Location | Parent_ID ----------------------------- 1 | USA | NULL 2 | Oregon | 1 3 | Portland | 2 Now say I have a worker who had credentials at the national headquarters. I can create a flat view that connects him directly to his location - say a view that only allows someone who works at the national office to log in. I can also create a view that goes three nodes deep as ID/Location Pairs: Loc_ID/Location/Par_ID/Par_location/Gpar_ID/Gpar_location How can I create a view that looks like this? Wrk_ID | Worker | Loc_ID | Location | Par_ID | Parent Loc | Gpr_ID | GParent Loc ---------------------------------------------------------------------------------- 1 | Jerry | 1 | USA | -- | -- | -- | -- 1 | Jerry | 2 | Oregon | 1 | USA | -- | -- 1 | Jerry | 3 | Portland | 2 | Oregon | 1 | USA This type of view would be easy to create in SQL, but I am having a hard time getting the result I want in Caspio.
  3. In the form datapage, you need to go to the advanced tab and set your dropdown field to grab the external parameter you are sending on load. If you don't, the form will just reload on submission as if it was new.
  4. Hi! I think I understand you correctly. I have a field with a lastnamefirst_usr and a user_id field. I'd like to use autocomplete based on the name, but have the user_id field entered in the form. So I start with a virtual autocomplete field that selects from lastnamefirst_usr. Then I add a cascading field that uses the autocomplete as a parent field, and selects the user_id field from the table. And I know ways to hide that second field. Is that correct? I originally had code that populated a dropdown and changed the value of the dropdown based on an autocomplete field, but that dropdown gets mighty big and I think doing the cascade select might fix that problem. I will give it a try. EDIT: This worked perfectly, and provided many more flexible sorting and presentation options. Thanks!
  5. The answer to this question doesn't really say enough to be helpful. The problem the original person is having is that same issue that I have. I have a table that stores several lookup key fields as IDs from other tables. I can set those fields to be editable dropdowns in the submission datapage form that will list a text field in the dropdown, but store the ID number in the key field on my record. But if I try to change the field on the form to an AutoComplete field, the only choices the wizard gives me for searching values on that form are other integer fields in the lookup table. If I go back to my original lookup table and change the key field from integer to text, then I can create a submission form that includes an autocomplete form that will search on a text field in a lookup table - however once I have made an autocomplete selection, the TEXT of my selection is stored in the key field, NOT the ID. I want to be able to have an autcomplete field that uses a text field in the lookup table but stored the ID number in the record updated by the form. Is this not possible?
  • Create New...