Forum Discussion

RoxanneZiegler's avatar
RoxanneZiegler
Qrew Cadet
22 days ago

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

 

 

 

formula query
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    21 days ago

    Roxanne,

    What does your application look like?    How are the tables related and where do you want to run your report?

    If I understand what  you are asking, I think this will do what you want:

     

    The Summary field on the Client table counts the Schedule records where the Outcome is No Show and the Date Created is during the current week.

    The No Show Count in the Schedule table is a Look Up field from the Client.   Every Schedule record, regardless of Outcome know how many No Shows happened this week.

    Your report on the Schedule table has the filter No Show Count Look Up >= 2.

    If your app is totally different than this, maybe it will give you an idea.   Otherwise, please share the ERD of your tables.

     

     

     

    • RoxanneZiegler's avatar
      RoxanneZiegler
      Qrew Cadet
      14 days ago

      Thank you. I have 2 tables 

      parent called Customer (holds client data) then it has tours as a child which shows how many times we hosted this client. 

      I created a summary field on the Customer table that shows me # of No Shows, which I can get on the tour table. But it shows me ALL customers even when I put in the filters - is equal or greater than 2

      But this shows me ALL Customers that have been with us twice. If I use the filter no show than it only shows me the no shows and not the other tours associated to that client.  so I need to scroll and visually identify them which as time goes on it will be huge.