ContributionsMost RecentMost LikesSolutionsRe: QB equivalent of VLOOKUP So the Category table has a relationship to the primary table, in that relationship is where you would pull the contact field through. How are you trying to add the contact field in the primary table? ------------------------------ Josh Hamilton ------------------------------ Re: Report consecutive dates If you create a formula checkbox field in the callout table and put in this query formula, it will look up the amount of callout records ahead of each record up to 4 days after its date that is related to the same employee. In this code, 6 = the callout date field id and 7 = the related employee field id so you will need to replace those numbers with the correct field IDs in your table. var date endDate = [date]+Days(4); var number d = Size(GetRecords("{6.OBF."&$endDate&"} AND {6.AF."&[date]&"} AND {7.EX."&[Related Employee]&"}")); If($d=4, "true", "false") If there are 4 records found, then it will mark the checkbox as true. You can then use a summary field in the relationship to check if there are any callouts with the checkbox marked as true and use that as your filter for a report to show you the employees with 5 consecutive callouts. ------------------------------ Josh Hamilton ------------------------------ Re: Report consecutive dates Might need some more information on what sort of fields you have in the callouts table and how it's used. Do you mean that you want a report that lists where an employee has 5 separate callout records assigned to them where I am assuming there is a date field of the callout that is used to see if those 5 are consecutive? ------------------------------ Josh Hamilton ------------------------------ Re: Populating same data on a click of a button So it sounds like you want some form of templating method to create a preset and then generate that preset whenever you have a 'New Station'. Couple ways you can do this but if you could tell me how you have set up your relationships/tables I can give you a better answer. The idea is that you set up a parent record as the source of your template, and using the copy feature you can recreate that parent record and include all the children records it has as well. ------------------------------ Josh Hamilton ------------------------------ Re: Button Group Conditional Display What you could possibly do is use create a condition that uses the 'UserRoles' function. Something like If(Contains(UserRoles("Name"),"Not Smart")=true, etc. or create it as a bool variable and for each hide/show condition use it in there like var bool role = Contains(UserRoles("Name"), "Not Smart"; var text rowOnebuttonOne = If($role=true and [Job Milestone]<>"Opportunity",$onebuttonhtml,""); I'm sure there is a better way to make it fit into your code, but I would say that those methods should at least point you in the right direction. ------------------------------ Josh Hamilton ------------------------------ How to indent a formula text field Hi, I want to be able to use the SearchAndReplace function to change { characters into ideally a new line and an indent. I know "\n" changes it to newline but not sure about indents. ------------------------------ Josh Hamilton ------------------------------ Re: Summary of a User FieldJust adding to this thread as I was looking for a solution to a similar problem. My goal was to create a way to filter records in the parent table so that a user would only see records in the parent table when they were assigned as a user in the child records. So in this case my result is a true/false value but can be adapted to create a list of users. To filter records based on if the user is assigned to a record in the child table; Create a formula text field in the child table that looks up the user fields on the child record and creates a list of the id's- List(";", UserToID([UserField1], [UserField2]) In the parent table I created a formula checkbox field and used the query function to do my look up using the formula below- var text u = UserToId(User()); var bool has = If(Size(GetRecords("{23.EX."&[Record ID#]&"} AND {31.CT."& $u &"}", [_DBID_TEAMS]))>=1, true, false); $has '23' was the parent related id field in the child table and '31' was the formula text field listing the User ID's and '[_DBID_TEAMS]' is the child table. Using the same principle, to create a List - User field; Create a formula text field in the child table that looks up the user fields on the child record and creates a list of the id's- List(";", UserToEmail([UserField1], [UserField2]) Create a formula List-User field in the parent table and use the formula; var text has = ToText(GetFieldValues(GetRecords("{23.EX."&[Record ID#]&"}", [_DBID_TEAMS]),31)); var user uone = ToUser(Part($has,1,";")); var user utwo = ToUser(Part($has,2,";")); var user uthree = ToUser(Part($has,3,";")); var user ufour = ToUser(Part($has,4,";")); var user ufive = ToUser(Part($has,5,";")); var userlist users = ToUserList($uone, $utwo, $uthree, $ufour, $ufive); $users Repeat the 'var user' up to 20 as User-List fields cannot exceed 20 entries. ------------------------------ Josh Hamilton ------------------------------ Re: Removing/Ignoring sub strings to create a list of itemsLast update, Just cleaned up the code to use fewer lines, here is the code that extends up to 10 line items; var text field = Trim(Mid([Body], Find([Body],"\"lineItems\"")+12, Find([Body],"\"orderAdjustments\"")-Find([Body],"\"lineItems\"")-13)); //Extracts the entire line items content var text removeAdjustOne = If(Contains($field,"\"adjustments\"")=true,SearchAndReplace($field,Left(Mid($field, Find($field, "\"adjustments\":"), Length($field)),Find(Mid($field, Find($field, "\"adjustments\":"), Length($field)),"]")+1),"")); //Removes the adjustments content from the first line item var text removeAdjustTwo = If(Contains($removeAdjustOne,"\"adjustments\"")=true,SearchAndReplace($removeAdjustOne, Left(Mid($removeAdjustOne, Find($removeAdjustOne, "\"adjustments\":"), Length($removeAdjustOne)), Find(Mid($removeAdjustOne, Find($removeAdjustOne, "\"adjustments\":"), Length($removeAdjustOne)),"]")+1),""),$removeAdjustOne); //Removes the adjustments content from the second line item var text removeAdjustThree = If(Contains($removeAdjustTwo,"\"adjustments\"")=true,SearchAndReplace($removeAdjustTwo, Left(Mid($removeAdjustTwo, Find($removeAdjustTwo, "\"adjustments\":"), Length($removeAdjustTwo)), Find(Mid($removeAdjustTwo, Find($removeAdjustTwo, "\"adjustments\":"), Length($removeAdjustTwo)),"]")+1),""),$removeAdjustTwo); //Removes the adjustments content from the third line item var text removeAdjustFour = If(Contains($removeAdjustThree,"\"adjustments\"")=true,SearchAndReplace($removeAdjustThree, Left(Mid($removeAdjustThree, Find($removeAdjustThree, "\"adjustments\":"), Length($removeAdjustThree)), Find(Mid($removeAdjustThree, Find($removeAdjustThree, "\"adjustments\":"), Length($removeAdjustThree)),"]")+1),""),$removeAdjustThree); //Removes the adjustments content from the fourth line item var text removeAdjustFive = If(Contains($removeAdjustFour,"\"adjustments\"")=true,SearchAndReplace($removeAdjustFour, Left(Mid($removeAdjustFour, Find($removeAdjustFour, "\"adjustments\":"), Length($removeAdjustFour)), Find(Mid($removeAdjustFour, Find($removeAdjustFour, "\"adjustments\":"), Length($removeAdjustFour)),"]")+1),""),$removeAdjustFour); //Removes the adjustments content from the fifth line item var text removeAdjustSix = If(Contains($removeAdjustFive,"\"adjustments\"")=true,SearchAndReplace($removeAdjustFive, Left(Mid($removeAdjustFive, Find($removeAdjustFive, "\"adjustments\":"), Length($removeAdjustFive)), Find(Mid($removeAdjustFive, Find($removeAdjustFive, "\"adjustments\":"), Length($removeAdjustFive)),"]")+1),""),$removeAdjustFive); //Removes the adjustments content from the sixth line item var text removeAdjustSeven = If(Contains($removeAdjustSix,"\"adjustments\"")=true,SearchAndReplace($removeAdjustSix, Left(Mid($removeAdjustSix, Find($removeAdjustSix, "\"adjustments\":"), Length($removeAdjustSix)), Find(Mid($removeAdjustSix, Find($removeAdjustSix, "\"adjustments\":"), Length($removeAdjustSix)),"]")+1),""),$removeAdjustSix); //Removes the adjustments content from the seventh line item var text removeAdjustEight = If(Contains($removeAdjustSeven,"\"adjustments\"")=true,SearchAndReplace($removeAdjustSeven, Left(Mid($removeAdjustSeven, Find($removeAdjustSeven, "\"adjustments\":"), Length($removeAdjustSeven)), Find(Mid($removeAdjustSeven, Find($removeAdjustSeven, "\"adjustments\":"), Length($removeAdjustSeven)),"]")+1),""),$removeAdjustSeven); //Removes the adjustments content from the eighth line item var text removeAdjustNine = If(Contains($removeAdjustEight,"\"adjustments\"")=true,SearchAndReplace($removeAdjustEight, Left(Mid($removeAdjustEight, Find($removeAdjustEight, "\"adjustments\":"), Length($removeAdjustEight)), Find(Mid($removeAdjustEight, Find($removeAdjustEight, "\"adjustments\":"), Length($removeAdjustEight)),"]")+1),""),$removeAdjustEight); //Removes the adjustments content from the ninth line item var text removeAdjustTen = If(Contains($removeAdjustNine,"\"adjustments\"")=true,SearchAndReplace($removeAdjustNine, Left(Mid($removeAdjustNine, Find($removeAdjustNine, "\"adjustments\":"), Length($removeAdjustNine)), Find(Mid($removeAdjustNine, Find($removeAdjustNine, "\"adjustments\":"), Length($removeAdjustNine)),"]")+1),""),$removeAdjustNine); //Removes the adjustments content from the tenth line item var text itemlist = ToText(Split($removeAdjustTen, "},")); $itemlist ------------------------------ Josh Hamilton ------------------------------Re: Removing/Ignoring sub strings to create a list of itemsOkay, so I found a solution. Basically just switched the order of the process. Used this before I did the split to find each instance of adjustments in the text and remove it out. var number adjustOneStartId = Find($field, "\"adjustments\":"); var text newAdjustOneStart = Mid($field, $adjustOneStartId, Length($field)); var number adjustOneEndId = Find($newAdjustOneStart,"]")+1; var text adjustOne = Left($newAdjustOneStart, $adjustOneEndID); var text removeAdjustOne = SearchAndReplace($field, $adjustOne,""); var number adjustTwoStartId = Find($removeAdjustOne, "\"adjustments\":"); var text newAdjustTwoStart = Mid($removeAdjustOne, $adjustTwoStartId, Length($removeAdjustOne)); var number adjustTwoEndId = Find($newAdjustTwoStart,"]")+1; var text adjustTwo = Left($newAdjustTwoStart, $adjustTwoEndID); var text removeAdjustTwo = SearchAndReplace($removeAdjustOne, $adjustTwo,""); var text itemlistUnfiltered = ToText(Split($removeAdjustTwo, "},")); Now this isn't a perfect solution, as it has a hard limit on items it can process. If there is a way I can make first find the frequency "adjustments" appears in the text at the start and then have it somehow run the process that many times... that would be ideal but I think I would be needing to create a code page and go down that path to run a javascript function. ------------------------------ Josh Hamilton ------------------------------ Removing/Ignoring sub strings to create a list of itemsI have a text field that contains content from a webhook. Looks like the following; [{"id":1234,"weight":0,"length":0,"adjustments":[],"description":text},{"id":1235,"weight":0,"length":0,"adjustments":[{"id":2013,"lineItemId":1235,"name":"text","value":1},{"id":2014,"lineItemId":1235,"name":"text","value":1}],"description":text}] Is there a way using formula fields to either: A) Create a list of just the parent {}, so the adjustments content within are included in the parent list item B) Remove out all the adjustments content entirely The goal is to get that content into a list that would look like the following; 1.{"id":1234,"weight":0,"length":0,"adjustments":[],"description":text} 2.{"id":1235,"weight":0,"length":0,"adjustments":[{"id":2013,"lineItemId":1235,"name":"text","value":1},{"id":2014,"lineItemId":1235,"name":"text","value":1}],"description":text}] The amount of id's and adjustments is not locked and can be more than what I have shown in the example. At the moment I am using this to clean it up but it doesn't work when a parent id has more than one adjustment in it. var number startFieldId = Find([Body],"\"lineItems\"")+12; var number endFieldId = Find([Body],"\"orderAdjustments\""); var text field = Trim(Mid([Body], $startFieldId, $endFieldID-$startFieldId)); var text itemlistUnfiltered = ToText(Split($field, "},")); var text itemOne = Part($itemlistUnfiltered,1,";"); var number itemOneAdjustStartId = Find($itemOne, "\"adjustments\":"); var number itemOneAdjustEndId = Find($itemOne,"]")-$itemOneAdjustStartId; var text itemOneAdjust = Mid($itemOne, $itemOneAdjustStartId, $itemOneAdjustEndId); var text itemOneClean = SearchAndReplace($itemOne, $itemOneAdjust, ""); var text itemTwo = Part($itemlistUnfiltered,2,";"); var number itemTwoAdjustStartId = Find($itemTwo, "\"adjustments\":"); var number itemTwoAdjustEndId = Find($itemTwo,"]")-$itemTwoAdjustStartId; var text itemTwoAdjust = Mid($itemTwo, $itemTwoAdjustStartId, $itemTwoAdjustEndId); var text itemTwoClean = SearchAndReplace($itemTwo, $itemTwoAdjust, ""); var text itemThree = Part($itemlistUnfiltered,3,";"); var number itemThreeAdjustStartId = Find($itemThree, "\"adjustments\":"); var number itemThreeAdjustEndId = Find($itemThree,"]")-$itemThreeAdjustStartId; var text itemThreeAdjust = Mid($itemThree, $itemThreeAdjustStartId, $itemThreeAdjustEndId); var text itemThreeClean = SearchAndReplace($itemThree, $itemThreeAdjust, ""); var text itemFour = Part($itemlistUnfiltered,4,";"); var number itemFourAdjustStartId = Find($itemFour, "\"adjustments\":"); var number itemFourAdjustEndId = Find($itemFour,"]")-$itemFourAdjustStartId; var text itemFourAdjust = Mid($itemFour, $itemFourAdjustStartId, $itemFourAdjustEndId); var text itemFourClean = SearchAndReplace($itemFour, $itemFourAdjust, ""); var text itemFive = Part($itemlistUnfiltered,5,";"); var number itemFiveAdjustStartId = Find($itemFive, "\"adjustments\":"); var number itemFiveAdjustEndId = Find($itemFive,"]")-$itemFiveAdjustStartId; var text itemFiveAdjust = Mid($itemFive, $itemFiveAdjustStartId, $itemFiveAdjustEndId); var text itemFiveClean = SearchAndReplace($itemFive, $itemFiveAdjust, ""); var text itemSix = Part($itemlistUnfiltered,6,";"); var number itemSixAdjustStartId = Find($itemSix, "\"adjustments\":"); var number itemSixAdjustEndId = Find($itemSix,"]")-$itemSixAdjustStartId; var text itemSixAdjust = Mid($itemSix, $itemSixAdjustStartId, $itemSixAdjustEndId); var text itemSixClean = SearchAndReplace($itemSix, $itemSixAdjust, ""); var text itemlist = List(";", $itemOneClean, $itemTwoClean, $itemThreeClean, $itemFourClean, $itemFiveClean, $itemSixClean ); $itemlist ------------------------------ Josh Hamilton ------------------------------