ContributionsMost RecentMost LikesSolutionsRe: Snapshot fields and Importing records Hi Mark, You have the data flow correct. Over the years, we started with manual data entry of the Child records, and the snapshots worked just fine, once I figured out the chicken& egg issue with the summary and look up fields. Then we were required to use a 3rd party to gather the data, and used excel to verify and create the CSV uploads. We had some issues when the same client (parent) had multiple visit records (children), so we split the upload so the first upload had all unique client IDs(parents). The CSV upload had no access to the parent & child tables to determine ahead of time which records might be "new". My current project to streamline the whole process within Quickbase, allowed me to have access to parent & child tables, so I know ahead of the import, which records should be new. I set up the Table to table import and Pipeline to run it with that in mind. Unfortunately, this last iteration is not reliable. I'll probably throw an "override New" value on the child table, populate that from source worktable, and take the snapshot from a formula field that looks at both the parent look up field or the override field for the final value. It isn't worth my time to create a pipeline to try to populate another field. The child table does get the occasional, valid, manual entry record, so I need to keep that part working the same way. thanks for the feedback. ------------------------------ Andrea Best ------------------------------ Snapshot fields and Importing records I'm trying to streamline some manual processes, and running into inconsistent results. I'm importing data into a child table. It has 4 snapshot fields that are set from the parent record. The child data is coming from a 3rd party, cleaned up (ex. making sure parent record id is correct). The goal is to get the clean up out of Excel, and simplify the upload to the child table. The fields on the parent record used for the snapshots are based on summary data from the child table. Specifically - based on the type of visit, if the Minimum visit date = Maximum visit date, then the child record being added must be new. It is the "new flag" that is used for the snapshot on the child record. FWIW - as weird as it sounds, yes the summary fields do appear to account for the new record as it is being added. I've moved the clean up to a worktable in Quickbase, no problem. I wanted to use pipeline to move the final data from the worktable to the Child Visit table. I created the pipeline, (simple API_RunImport) ran it, but only 34 of the expected 245 records had the correct snapshot values. Bummer. Reviewing the lookup fields on the parent & child, the values were correct. Just the snapshot wasn't set correctly. I cleared the data, and manually ran the defined TableToTable import. This time only 3 of the expected 245 records had the snapshot set correctly. Bummer again, going in the wrong direction. I cleared the data again, downloaded the data to CSV, then manually did Import from CSV. This time I got 234 out of 245 records set properly. The ones that were incorrect, I could possibly account for. The child table has 4 different snapshot fields, related to different types of visits. If a parent record client had 2 types of new visits, there would be 2 records in the dataset. Only 1 would have the new snap field properly set. The other would not. I'm guessing it probably has to do with QB caching parent record data. I could solve this by separating imports by visit type. Does anyone have a suggestion on how to get the pipeline to add records with correct snapshot values? I would consider this a bug, but would like to know what the community experience is with snapshots and data imports. For those wondering about repeat visits, I have those separated out, and they would be a different import, just to prevent issues with the New visit calculations. thanks, ------------------------------ Andrea Best ------------------------------ Re: Random "number assignment" I've been watching this thread for a bit, but haven't chimed in, since it sounded like folks were solving the issue. But now it is sounding far more complicated. I had a similar need - Assign a random place in line for people making appointments coming to pick up free produce (food pantry). A random place was needed so that the same individuals wouldn't always be first in line. Original solution was pre-javascript ban, but I think it can be adapted for folks who know piplines etc (I haven't ventured there yet). We have moved to a different appointment method, so this solution was no longer needed. Parent table - Pantry dates - defines a date the pantry would be open, how many appointment could be made. Has a text field to hold a comma delimited list of random numbers, and a button to generate the numbers. The button calls a .js code page, with parameters of the record id, and max number of random numbers to generate. The code page generates the list of unique random numbers, and updates the Parent record with the list. As appointments are added, each appointment is assigned the next random number in the list Part([Line Numbers], [# of Produce appointments], ",") Appointments are not allowed to be deleted, to prevent a number from being reused. For this thread, if the child records already exist, then those versed in pipelines, could make a pipeline run through the list of records and assign the next random number in the parent list. I think this fits the original random number issue the original poster had. It might not fit the greater discussion later posters have. Hope this helps if someone needs a simple random number solution. The pertinent javascript code for generating the random number line: qb_maxnum is the maximum number to generate. Initialization forced anything lower than 10 to be 10. The random number methodology I pulled from the web a number of years ago, and I believe the logic is solid. function PopulateNumbers() { /* First routine called. This will handle generating the array of random numbers. Basic overview Populate the array Loop through the array to shuffle the numbers */ for (var widx = 0; widx < qb_maxnum; widx++) { // Load each number into the array numberline[widx] = widx + 1; } // end for loop load each number numberstring = ""; //Clear it out for (var widx = qb_maxnum - 1; widx > 0; widx--) { // Shuffle each number into the array //select a random number (index) between 0 and the loop idx randomnum = Math.floor(Math.random() * (widx)); tempnum = numberline[widx]; //save the value in the current spot numberline[widx] = numberline[randomnum]; //swap it with the random value numberline[randomnum] = tempnum; //replace value so its available numberstring += numberline[widx] + ", "; // Tack the used number on the end of the number string } // end for loop randomize each number //Add the last number to the string numberstring += numberline[0]; } //end populate number line ------------------------------ Andrea Best ------------------------------ Re: Notifications to distribution groups (Outlook DGs)Hmm, it seems a bit excessive to pay for a seat that no one will actually use. Since you use Microsoft365, couldn't you send the email to a currently registered user (like yourself), and have a rule that says if Email is from Quickbase, and subject contains report name, forward this on to the distribution group? You can name your report anything you want to make it unique enough for a rule to recognize. I haven't tried it. It's on my list to attempt in the future when I have the time. Just an idea. ------------------------------ Andrea Best ------------------------------ Re: Notifications to distribution groups (Outlook DGs)Just a thought, as I ran into something similar apart from Quickbase. A Microsoft365 admin needs to set the distribution group to allow emails from outside sources. Since your tests with individual recipients works, I'm guessing the issue is on receiving end. ------------------------------ Andrea Best ------------------------------ Re: Conditional Logic in Exact FormsLate to the discussion, but I only found this looking for help on a similar topic. I have an exact form that uses lots of conditionals. If the field is filled in, it prints the field. If it isn't filled in, then it prints an alternative, such as a blank line for the recipient to fill in. Here is an example City ~=if(f['49'] == "") {" ";} else {f['49'];}~ It has been a while since I put this together, so others more conversant in the language (javascript?) can correct my explanation. Field ID 49 is the City on my table. The whole script must be enclosed with Tildes, "~" "Code" begins with equal "=" Must have Parens around the Code that will evaluate to true/false for the If. You can use && (and) and || (or) for multi part boolean expressions. Values of Quickbase fields are accessed by "f", Bracket [, single quote ', field id number (49 in this case), single quote, end bracket ], ex: f['49']. In my example, I'm checking to see if the City field is blank. == is equal comparison operator. Off the top of my head I don't remember if not equal is <> or !=. Curly braces {} surround the Results you want for True. MUST END WITH A SEMICOLON ; Add "Else" with curly braces if you want an alternate value for False put on the form. It also must end with a semi colon. Any Text with double quotes make sure it is the "Straight up and down" double quote, not the "left or right leaning" double quote that MS Word likes to use. It will fail every time, and is difficult to see with old eyeballs. If you have a lot of these, the space it takes to type it in makes the form look wacky. It will still evaluate to the proper spacing. Adding Bold or Underline to your text will make the results also have the same characteristics. Hope this helps. ------------------------------ AndreaLBest AndreaLBest ------------------------------ Re: Previously working JAVA Scripts do not work anymoreFor what its worth, tldr: I changed the definition of the QuickBase button, unchecking the "open target in a new window", and now the button works for me. Longer: Chrome version 76.0.3809.100 did change behavior. I didn't understand most of what the change talked about. It did cover issues regarding opening a new window, copying context, and queueing up clicks. Chrome folks said the changed behavior is the correct behavior, and will not be changing it back. For my application, I did not need or want a new window opened. I was surprised to see the checkbox for my button filled in to open a new window. It was worth experimenting to remove the check, and looks like that solved the problem for me. Good luck all, hope you find solutions that fit your applications. Re: Previously working JAVA Scripts do not work anymorejavascript:" & "var qb_rid=" & [Record ID#] & ";" & "var qb_maxnum=" & [Maximum Appts] & ";" & "$.getScript('" & URLRoot() & "db/bjxxxxxx?act=dbpage&pagename=ProduceNumbers.js');void(0);" Re: Previously working JAVA Scripts do not work anymoreI have the same problem. JavaScript button was working in Chrome, now fails. It DOES still work using Edge. Similar to original poster, my button calls a .js page, which does some calculations, then updates a record. It isn't complex. I'm not an adept javascript user, but my simple debugging techniques (an alert inside the code) indicate the page isn't loading at all. Chrome settings allow JavaScript, and pop ups.