Jump to content


Caspio Ninja
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by vanderLeest

  1. I tried to implement all the suggestions above in my scenario, and after quite of bit of trial and error the script below on a Details page of a Report shows the edited values in dollars while you type in Txt help fields. BeforeFormSubmit these text values are transformed in numbers and stored in the currency fields (to allow for calculations) It's not straightforward but it is useful workaround for something that should have been fixed in Caspio vanilla years ago <script type="text/javascript"> document.addEventListener('DataPageReady', assignEvent); function formatAsDollars(el) { el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,"); el.value = el.value ? '$' + el.value : ''; } function assignEvent(){ let fields = ["BudgetTxt", "SATotalTxT"]; fields.forEach(element => { element = "EditRecord" + element; document.getElementById(element).onkeyup = function() { formatAsDollars(this); } document.getElementById(element).onchange= function() { formatAsDollars(this); } }); } document.addEventListener('BeforeFormSubmit', function (event) { document.getElementById("EditRecordSubAwardTotal").value = document.getElementById("EditRecordSATotalTxT").value.replace(/$|,/g, ""); document.getElementById("EditRecordBudget").value = document.getElementById("EditRecordBudgetTxt").value.replace(/$|,/g, ""); }); </script>
  2. If you embed your Caspio pages in a Content Management System (CMS) like Sitefinity, only SinJunYoung's suggestion works. In this case, truncating is imported for fields that can have long text strings. I tried JolliBeng's suggestion as it looked more straightforward, but neither setting pixels or percentages work; they work/have an effect if you view/render the page as a native Caspio Page.
  3. NiceDuck's suggestion works for my app only to a certain extent. Sadly, the log-out time is stored against the previous Session_ID of the user. It appears that the authentication routine stores the status of the fields in the authentication table at the moment of authentication, and not refreshes them after the Session_ID gets updated (in my case via a trigger). I track the user's login via a hidden Auto Log In page in the T_User_Log table. I have a trigger on insert in that T_User_Log table that copies the random Session_ID into its equivalent in the T_User table. Next, I have a Log Out page that should put a timestamp in the Log table for this Session_ID. The solution is to create a View (in my case Q_Current_User_Sessions) based on the T_User and T_User_Log tables. Inner Join the tables on both their respective User_ID and Session_ID fields and set the latter to be editable. Fields to include are Log_User_ID, Log_Session_ID and Log_Out_Time (without criteria). Design the DP User_Log_Auto as a Details Report, select Q_Current_User_Sessions as the source, and require authentication. Restrict record access based on User_ID identity matching Log_User_ID. Set no filters, and select Log_Out_Time as the only field for the Details Page. Hide Log_Out_Time to receive on load the system parameter Timestamp. Hide the form with a style in the Header and add an HTML Block at the bottom of the page elements with an auto-submit script. After record update, close the window, and - voila - we have working (and hidden) User_LogOut_Auto page.
  4. I found my error: I inserted the id (id="btnAdd") to the input section instead off as part of the div parameters. <div id="btnAdd" style="display:none; justify-content:flex-end; width:50%; padding:0;"> <input class="cbSubmitButton" type="button" ..... (Before I found this error, I also set the page to be responsive.) Thanks so much for you great solution, Sandy159 Floris
  5. Thanks Sandy159, Your solution and set-up looked very promising. However something in my set up is not exactly right. According to the 'hiding the map' article, in the used Localization Settings, I customized Element ID #351 (the No Records Found message) by adding div tags around the message to <div id="norecord"><h1>No records found.</h1></div>. I changed the entry in the Header of the Results page (i.e. display:none; and added id="btnAdd") to look like <div style="display:none; justify-content:flex-end; width:50%; padding:0;"> <input class='cbSubmitButton' type="button" id="btnAdd" onclick="location.href=''https://[pagelink]?XC_ID=[@field:XC_ID]';" value="Add Contact" /> </div> and added your function to the footer script function func_NoRec_Btn() { if (document.getElementById('norecord')) document.getElementById('btnAdd').style.display = "flex"; } document.addEventListener('DataPageReady', func_NoRec_Btn); Sadly the button does not appear (although the extra row is there) when the search gives no records. By the way, AJAX loading is NOT disabled and the button shows when display is set to flex in its div style. It seems the function is not setting the display. Any idea where my set-up goes wrong?
  6. I solved the placement of the button. No luck yet hiding it when no records found. <div style="display:flex; justify-content:flex-end; width:50%; padding:0;"> <input class='cbSubmitButton' type="button" onclick="location.href='https://[pagelink]?XC_ID=[@field:XC_ID]';" value="Add Contact" /> </div>
  7. On a search result page, I want to show a button to open up a submission form, when there are no records found for the selection criteria and I want to align that button to the right of the form. The tabular results are shown under the search boxes. I have attached two pictures that show the current and desired situation for either Records Found and No Records Found
  8. Just what I needed, for a repurposed search (to function as background information for a tabular report)
  9. I have setup 2FA for external users. To enable an extra level of security (for malicious hackers) as well as an already trusted user interface/landing page for internal users, my idea is to route internal users through another application named PALMS, which they are already familiar with. As they log into PALMS, their login instance receives a unique token (picked from a table with 1 Million predetermined tokens). My Caspio app will have an identical table of with the same 1M tokens. The link from a PALMS page to the Caspio App will have the following structure: <a href="URL?AppKey=[@Token]&Emp_ID=[@Emp_ID]">Caspio App</a>. Can I create something on landing on a Caspio page that will pick up the Token and Emp_ID from the URL and to store it in table and verify a my version of the Token table and Staff table? Any help appreciated. NB Single Sign-On (SSO) via Caspio is only available through a Corporate plan and hence too expensive for us.
  10. Hi again, CoopperBackpack, I changed the page's source from a View to a Table, and the two virtual fields are now calculated values based on a select statement, drawing information from a View. Now the JS script no longer works. Do you have any ideas why, and for a workaround? Uncaught TypeError: Cannot read property 'value' of null at HTMLDocument.<anonymous> (8777500065adaf522f0942fca682?ID=414:935) at f_dataPageReadyEvent.dispatchEvent (8777500065adaf522f0942fca682?ID=414:2473) at f_dataPageReadyEvent.init (8777500065adaf522f0942fca682?ID=414:2473) at new <anonymous> (8777500065adaf522f0942fca682?ID=414:2474) at 8777500065adaf522f0942fca682?ID=414:2473 Floris
  11. Starting with LittleMsGinger's workaround (and using her validation trick based on virtual fields ), Dmytro from the helpdesk and I came up with the following routine, which also works for a Direct Deploy URL (NB. Testing is best done via an incognito window of your browser, and by having a log-out page open.) Two-Factor Authentication – Two Tables, Two DataPages --> An auto-submit Submission DataPage ‘Log_Auto_Form’ for tracking log-in records in the T_Log table. On entering an email and a standard password via a User Authentication, the Log_Auto_Form page emails a One-Time Password (OTP) - in the form of a Random ID created for the T_Log record - to the User, with auto-submit JavaScript (JS) for redirecting to: --> A Single Record Update DataPage named ‘OTP_Validation’, that is based on the same T_Log table, with L_RecordID as its Unique ID field (and [@L_RecordID] as the Parameter name). Table T_Log has six fields: L_RecordID and L_OTP as Random IDs - I choose different prefixes for each -, L_User_Email as Text, L_Tm_LogIn and L_Tm_LogOut as Date/Time and L_TimeStamp as a TimeStamp (on Insert). User Authentication uses (at least) two fields of the T_Users table: U_Email and U_Password. While you create the Submission Log_Auto_Form, tick the box for ‘On Exit, pass ID …’ on the Select Fields screen of the Web Form Wizard. Select only L_User_Email as a field for the form. On load, receive the U_Email authentication field as the value for this field. Add an HTML Block to the page - make sure it is always at the bottom of the DataPage Elements. Copy and Paste the following JS script to auto-submit the form: <script> function f_submit() { document.getElementById("caspioform").submit(); } document.addEventListener('DataPageReady',f_submit); </script> Set ‘Go to a new page’ as the Destination of the Log_Auto_Form, paste the link to the OTP_Validation page in the URL field, and add a connect string with the L_RecordID of the T_Log table, which is the unique ID for the Single Record Update OTP_Validation page, for example https://nnnnnnnn.caspio.com/dp/….?L_RecordID=[@field:L_RecordID] In Messaging Options, enable an Acknowledgement Email that will send the One-time Password to the current User (U_Email), for example “Your One-Time Password (OTP) is [@field:L_OTP]. This OTP is valid for one session and will expire 2 hours from now.” In the OTP Validation page, only select the field L_Tm_Login, and add 3 virtual fields to the Datapage Elements. (The virtual fields are for LittleMsGinger’s validation trick below.) Set the Form Element of L_Tm_Login to TimeStamp, which will automatically hide the field. (NB. the time difference between L_Tm_Login and L_TimeStamp in the T_Log table is the time it takes the User to copy the OTP from their email to the page.) Label Virtual2 as ‘One-Time Password’ and set it to Text Field (You can set the field width to the length of L_OTP, if you wish.) Make Virtual3 a Calculated Value, set the Formula to 0, and tick Hide Field in the Advanced Options. Make Virtual1 a Calculated Value, set the Formula to CASE WHEN '[@cbParamVirtual2]' = '[@field:L_OTP]' THEN 1 ELSE 1/[@cbParamVirtual3] END, and tick Hide Field in the Advanced Options. The latter formula will throw an error (Division by 0, which stops the record from being submitted) if the inserted OTP is not the same as the OTP for the current L_RecordID. NB. For testing the functionality, do not hide Virtual1 and add L_OTP to the form. Set ‘Go to a new page’ as the Destination of the OTP_Validation, paste the link to the first page the User needs to interact with in the URL field. _____________________________________________________________________________ I hope this helps someone out there. Let me know if you've found bugs or ways to enhance the routine. Floris
  12. I would love to have a bit more detailed advice from NiceDuck too. I'm stuck at the same point.
  13. Thanks so much LittleMsGinger, Three questions: 1. Can you describe the whole process, and how these DataPages each play a roll? 2. What are the DataPages you point to as the value for window.location in the javascripts, for both the Acknowledgement Email and Standalone Login Screen? 3. How did you finish the Details Page (at step 6) ? Does direct to the original/intended destination page? Floris
  14. Thanks CoopperBackpack, your solution worked (almost perfectly). I put your JavaScript in the header of the DataPage, and changed the option around, as title == title1, the innerHTML of the H4 header should read "Add your Invoice and Financial Statement Report". Floris
  15. Hi CoopperBackpack, I will definitively give your suggestion a go (next week) and let you know how I go. Floris
  16. I want to change a header of submission datapage based on a comparison between two virtual fields on the page. The virtual fields are fixed values for the record drawn from an underlying query, using dropdown values. If Virtual 2 <> Virtual 3 a section with a submission field for the Financial Statement Report is hidden (via Rule 1) , and if Virtual 2 = Virtual 3 the same field is set to be required (via Rule 2). I did not create the header, but it looks currently like this: <div class="container mt-3 mb-3"> <div class="row"> <div class="col mb-3" style="display:inherit !important;"> <h4>Add your Invoice and a Financial Statement Report</h4> <a href="https://c4ffn695.caspio.com/folderlogout" class="ml-auto btn btn-secondary">Logout</a> </div> </div> <div class="row"> <div class="col"> If Virtual 2 <> Virtual 3, I want to header to read 'Add your Invoice' and if not 'Add your Invoice and the Financial Statement Report' I tried (to no avail) - with different header values - the following script in the Footer <script type="text/javascript"> document.addEventListener('DataPageReady', function (event) { if ("[@cbParamVirtual2]" == "[@cbParamVirtual3]" ) { document.querySelector("h4").innerHTML="Some title"; } else { DataPage, you may try document.querySelector("h4").innerHTML="Other title"; } }); </script> But I am no JS expert Any help sincerely appreciated Floris
  17. The issue resides within an incorrect table name in the join statement in both select queries: The join is pictured with the (alias name of the) T_PA_SubAwards_1 table, it should be with (the original) T_PA_SubAwards. To me this is counter-intuitive (as the line above 'defines' T_PA_SubAwards as T_PA_SubAwards_1), that said, somehow it makes more sense too. ;-) The pictured task will update the SA_Last_MS field in the table T_PA_SubAwards with the Max value of the field PeriodNo in the subsidiary T_PA_Milestones table, but only if that MAx value is different than the current value, or if SA_Last_MS is still blank.
  18. I want to update the SA_Last_MS field in the table T_PA_SubAwards with the Max value of the field PeriodNo in the subsidiary T_PA_Milestones table. The two tables are linked via the fields SA_RecNo and MS_SA_ID. The task pictured runs but gives a value of 2 for all SubAwards with Milestones, and that is correct for most of them, although two SubAwards have 4 and 5 milestones, and these are erroneously set to 2 too. Can someone tell me what is going on?
  19. Thank you both LittleMsGinger and CoopperBackpack so much Once you know these options, it is easy. Sadly enough, I did not find them myself intuitively.
  20. I would like to format the auto-email on submit of an update form as neatly aligned table Vendor Number [@SA_Vendor_ID] Invoice Number [@MS_InvNo] Current Date [@Date()] Total Amount [@MS_InvAmount] Currency (fixed “USD”) SES Number [@MS_SESNo] PO Number [@SA_Purch_Order] How can I make this happen?
  21. The limit for Number fields is 15 digits (including a floating decimal), and 10 digits for an Integer field. A minus sign counts as a digit in the case.
  22. Thanks SunakoChan. to create a rendering of a period between a Start and and End date like Jan '19 - Feb '20, Dmytro and I came up with the following formula (for a formula field), using your substring idea: CONVERT(VARCHAR, (SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ((Datepart(month, [@field:MS_StartDt])) * 4) - 3, 3))) + ' ' + Char(39) + CONVERT(VARCHAR, (Right(Year([@field:MS_StartDt])+ 1,2))) + ' - ' + CONVERT(VARCHAR, (SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ((Datepart(month, [@field:MS_EndDt])) * 4) - 3, 3))) + ' ' + Char(39) + CONVERT(VARCHAR, (Right(Year([@field:MS_EndDt])+ 1,2)))
  23. Thanks this works I used: CASE WHEN LEN([@field:MS_InvAmount]) > 0 AND Len([@field:MS_Budget]) > 0 AND Round([@field:MS_InvAmount],0) > Round([@field:MS_Budget],0) THEN '<span style="color:red;">'+cast([@field:MS_InvAmount] as nvarchar)+'</span>' ELSE CAST([@field:MS_InvAmount] as nvarchar) END But sadly, instead of $300,000 in red, I get 300000.00 in both the Result Page as well as in the Details Page of my Tabular Report DataPage.
  24. Hi Jan How can I compare two currency fields - set the Display Only on a Report Details Page - and if the Invoiced value is higher than the Budget value, color the text of Invoiced value red and its background yellow? Thanks for your help
  25. A Report Detail DataPage DP0 shows an overview of Milestone Information, and using the Milestone_ID, I want to open a second DataPage (DP1) unseen; DP1 is based on another View, and should generate / automatically download a one record XML spreadsheet on the PC of the user. The idea is that users click on the link in DP0, and the download should start automatically. NB: The link to DP1 on DP0 will only be visible when all necessary criteria for DP1 are met. Workflow: An in-house team will be the only ones having access to DP0, via group authentication. They will use DP1 to check the completion of the current 'open' milestones. When all reports are approved and a purchase order has been entered, they should be able to download the XML sheet, and mail this template from their PC to the another in-house office for actioning.
  • Create New...