Formula Query with options
Hi, Is it possible to sort the results from a formula query. I have a formula query to get list of dates from a table. I would like to get the latest of the returned dates. Is it possible to use options=sortorder-D in the formula query? var text Query = "{65.EX.'" & [PBG Number] & "'}"; var number NUM = Size(GetRecords($Query, [_DBID_ACTION_ITEMS])); If([Active_Ind] = true and $NUM > 0, ToText(GetFieldValues(GetRecords($QUERY,[_DBID_ACTION_ITEMS]),2)) )
Sum Overlapping Time/Remove Timeframe Gaps
Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records. Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.
Formula help
I am trying to accomplish this: I want a report that will show me clients in a week how many times a record was made with the filter No Show So a client is booked - he does not show on Monday, on the form he gets a Q of no show. then he shows up on Wed and we proceed with what we do. I want to run a report of only those customers. Now I can run a report that shows me all Q that = No Show and sort by Client but it only shows the no shows it does not show any of the other time they might of come. If I don't use the filter no show then I sort and group by customer and it shows me everything and then i can see it but there are hundreds and eventually more. I have a summary field that counts the qualified tours as a 1 and the no show as 0 but other things are 0 as well (other unqualified) QB tech support is sort of helping but it's not working and I am pressed for time. They originally said this - So I created a summary field on (child record) and filtered by created in a week. Then it was not clear on setting up the formula ( is it in report or another field - well I did both and got the same result. when I created If([Weekly No Show Count] = 2, true, false) the True was yellow no matter what I did on either the formula in the report or a new field. It had to do with the boolan Then I created a check box formula thinking that would help - If Q said NO SHOW check the box thinking I could make that work. First we need to create a summary field in the parent table: Name: Weekly No Show Count Summarize Field: [Child Record ID#] (or any unique field in the child table) Filter: Date created is within the current week. Second we can setup the formula: If([Weekly No Show Count] = 2, true, false) This formula will return true for customers who have exactly 2 child records within the given week. And for last we can create a report that would display those records. Report Type: Table Filters: Is No Show Twice This Week is checked (true) and then in the report table - when it said use those above filters is why I made a check box. I am leaving for a few days and didn't want to leave this hanging out in QB tech support world as they would of course close the case - I have messaged several times but to no avail - this case has been opened for a long time already
Date Formula
Hi all, please can someone help me! I have to create a date field that needs to auto populate when a certain tick box field has been selected. Basically we want to create a report to show a date that one of the tick boxes was selected. We already have an email alert for this tick box, but they need to run a report, so need to create an auto populated date field for this. My formula skills are shocking and need your expert advice! Thanks
IF AND Formula
I am trying to write a report formula that is triggered off a checkbox and two different dates that I would typically write an IF AND formula. Anyone know how to make this work? It is giving me an 'expecting ,' error. The Cybersecurity field is a checkbox. The Engagement Begins is a date field. The Cybersecurity Discovery Engagement Ends is a date field. The logic should read if the Cybersecurity checkbox is checked AND the Engagement Begins field is blank, then the field should read "Upcoming", but if the Cybersecurity checkbox is checked AND the Engagement Begins field is not blank, and the Cybersecurity Discovery Engagement Ends field is blank, then the field should read "Assigned", otherwise show nothing.
Calculating Variance in reports
I am trying to make a summary table where it shows revenue per month for each customer at a company. I want to add so in between each month, there is a change of revenue displayed. How can I do this. I have tried a report link for previous months and that did not end up working.Sum data from a column in parent but ignore duplicate data
Problem with data involving a Time Card Table and a Dispatch Table. one Time Card : many Dispatches Each tech has one Time Card per day and 2-3 Dispatches per day. The Techs daily Time Card Record ID# is entered into each Dispatch when created. Here is what the columns in the Dispatch tbl look like (with about 10 columns of misc dispatch data missing for this example): Example Attached This is one week of activity, and I want a summary report in the Dispatch tbl to show the total hours the techs were on the clock, to compare travel time, billable time, and clock time. This example (164:29) is all I can get to show up on a Summary Rpt. I need to have only one TOTAL CLOCK for each Related Time Card. The actual time for tech GO was 31:28 and for tech JD was 48:30 for a total of 79:58.Get a value from another entry in the same table
We've been using a QB Table to track snap shots of business health. Once a week we log some numbers across different tables. This isnt exactly what we do - but as an example it could be: how many orders placed how many orders delivered how many orders on hold how many customers outstanding on payment how much billed received how many customer feedback received etc etc and we do this for each "store" - let's assume we have 2 stores. Now here is my issue! I want to track PERCENTAGE CHANGE since last week. So it's the same table - but I want to compare, "orders placed" this week for this store, versus "orders placed" last week for the same store. I want to be a formula for a field which would show PERCENTAGE INCREASE OR DECREASE. ( and then we can get fancy and make charts, graphs, color code etc) Where I am having the issue is - how do I find the value of last weeks entry for this store?? let's assume each entry has a "week of" and a "store code" - so for example: 07-05-2024 and MAINSTORE. How do I get the # of orders placed for 07-28-2024 and MAINSTORE and have it auto populate? I would prefer to go this route where I can lookup last weeks #'s - so if I ever need to scale I can. Worst case is that I will choose 2-3 KPIs to log each week. Any ideas?
