Search the Community

Hello, I often use the following SQL code to create a calculated field which concatenates elements of a list with a character separating them (here is it a vertical bar "|"). For instance, here I am creating a list of all the common names for a given plantID (CPCNumber) in the table tblPlantCommonNames. Is there a way to create this code but remove the character from the end of the list? SELECT CAST( CommonName AS nvarchar) + N' | ' FROM tblPlantCommonName WHERE CPCNum = [@field:CPCNumer] for xml path(N'')

I have a table that includes the person's date of birth (DOB). I am able to create a virtual field in my Report DataPage that calculates the age from the DOB and display it. I also need to be able to search on age. How can I do searches such as: 1. Age is equal to x 2. Age is greater than x (minimum age is x) 3. Age is greater than minimum x and less than maximum y Thank you for any help on this. The application has to do with booking models, where age is an important factor.

Hi all, Is it easy to calculate the total number of records in a table to display on a data page? I was thinking of creating a landing page with summary data on it including some record totals. Thanks! JC

Hello - I need help for custom JS to compute and display the Calibration_Due in a submission form when the user inputs the Last_Calibration date and the Calibration_Frequency · Calibration_Due = Last_Calibration + Calibration_Frequency Where field types are Calibration_Due – date Last_Calibration – date Calibration_Frequency – number of months

Hello everyone - I have a Calendar dataPage and I added a Calculated field and a link in it. I need a JS to hide the Link I created if the Calculated Field is not NULL or blank

Is it possible to hide entire rows of data in a tabular results page based on a Calculated field Value using JS? Essentially, I have a search and report to return records based on predefined criteria. However, one criteria that I would like to filter by is not stored directly in the table (Table A). It is stored in another table (table B ). So I have a calculated field holding a SQL Query to return a number based on the records from the other table (table B ). I would like to then see if there is a way to hide all records returned that are returned with a Value of 0 from the Table A Tabular report. Is this possible? Additional Information: For several reasons, of which I don't want to waste your time or over-complicate the question, using views to combine the tables in a manner to accomplish is not desired. Nor would a solution dependent on Sorting or Grouped results be sufficient. I really would like to make these results entirely invisible/inaccessible. Thanks in advance for all of your time and assistance. Cooper

Hello, I am looking for help on a solution I am hoping to implement. I will do my best to be clear and include all necessary information but please let me know if you have any questions. I believe this can be accomplished with SQL but am not familiar enough with SQL to begin this on my own. I have a search and report datapage (tabular) that pulls information from Table A. Table A has columns including Client, Project, Asset, Task, and Status where a client can have multiple projects, a project can have multiple assets, assets can have multiple tasks and each task has a status (See Table A.png). Each Project in Table A can have several thousand records beneath it. I would like to use SQL to display a Percentage of all records within a particular Project (lets say project 1) that have a Status of "Complete". "1" will be passed as a parameter into the filter (Column=Project) using @Project_ID. I would like to display just the percentage of complete records and possibly a count of total records, both filtered by @Project_ID. I do not wish to actually display a full table. My ideal product would look something like the second attachment (Table A Completion Percentage). Further notes: I am absolutely open to changing the Datapage in order to accomplish this goal. I am not however looking to display this information in a chart as I really am only looking for the Percentage and do not need to display the percentage of other status' as well, as a pie chart would. Also I want to make sure that this only displays information from a single Project_ID at a time as this percentage will be a Client facing data page and I don't want them to see other clients or projects. I was able to accomplish this solution previously by adding a field to the table that entered a 1 if a record was updated with a Complete status and a 0 if updated with something else but this made the data quite messy and it complicated things if the data was updated via an import as the calculation field was never submitted or calculated. The calculation as I'm sure you guys know, only exists on the Datapage level and not within the database itself. Anyways, I am redoing this to avoid going down that road again. Thank you all so much in advance for any help you can provide. Cooper

Brand new to Caspio and have been using a trial to evaluate. Very close to signing up and using it as our vehicle of choice to build a new CRM system (we currently use Zoho) One question: I understand how to use calculated fields within reports, but it is not clear (if possible) how to make a field content dependent on another at the time of data entry. eg Business is introduced to us for which we pay a commission. Certain parties (through reputation or volume) are entitled/demand alternative terms. What I would like to know is - through a drop down list - is:- Q: Commission tariff: If (say) - (S)tandard, apply a set rate to each product. (populate field with a value (£/$). If (B)espoke, then enter the appropriate rate(s) Any help appreciated. Kind regards Greg Director