How to hide unnecessary rows in a data source?
Hi Everyone,
I have a screen form that connects to a data source with information regarding estimates.
Once an estimate changes to an invoice, we would like that info to become invisible to the person that uses the form.
Instead of deleting rows, there is a safe method where I could add a status field in the Data Source where it changes to an "Invoice" = 'Yes.' Then I could use filters not to show invoices.
However, I don't know how to implement this solution.
Would anyone know anything about this? Thank you for your help.
If your screen is an entry screen, and each field ties to that data source, then presumably there is a choices field that selects the rows. At the entry point where an estimate becomes and invoice, you should design a column in the data source, such as {{invoice}}, and change that to 'Yes' at that point. Then in the choices field, use a filter that only allows the user to see rows where NOT({{invoice}}) = 'Yes'. This should filter those rows.
If the form you are using is a listing screen, you'll want to use the same method of filter, but on the listing screen. And update the data source at the point it changes to an invoice for that column to include 'Yes'. Is this helpful? If not, please describe more of your screen.
Hi Victor, thanks so much for your help in solving this problem.
Not sure what kind of screen it is, but the fields are bound to a Data Source to display information.
To be more precise, I have two (2) data sources connected to the form. One data source connects to a "Drop Down List" field, which automatically fills a series of areas in the form. The other data source is a "List of Choices" type field.
I have tried to implement the recommended solution in the first data source, the "Drop Down List" type. As advised, I have added another column in the data source, which I called "Invoice" and a hidden field in the form. (Screenshots attached) Then, I added the NOT({{invoice}}) = 'Yes' formula. I wasn't sure where to attach it exactly, so I have tried both the "filter rows" criteria and then in the "Visibility" tab. (Screenshots attached). Unfortunately, none seemed to work. I must have made a mistake, but I can't identify it.
Is there a way to update the "Invoice" column with "Yes" or "No" automatically, or this solution only works if I manually make the change?
The greatest thing will be if there's a way to automatically hide (maybe even remove) the estimates in the data source once they turn into invoices in QuickBooks.
I'm so grateful for any help in this matter.
Robert, I much appreciate your input. I have tried the {{Invoice}}='No' formula, but without any luck. I most probably do something wrong, so I'll write in bullet points to list the steps:
1. Data source
Question: Does the data source needs to be built via a Data Source field, or is it OK if the data source connects to a Choices field? (Screenshots attached.)
My form has the latest - a Choices field with a data source. A series of areas in my form are also bound to the data source columns.
2. Add a column in the data source.
Do I leave the column blank? (Screenshot attached) Or, do I specify Yes or No?
3. Integrate formula: {{Invoice}} = 'No'
Where exactly do I integrate the formula? For some reason, none seem to work.
a) Filter rows, "use formula." (Screenshot attached)
b) Filter rows "add a criterion."
etc.
4. Create a hidden field that binds to the data source and column, with a dynamic field set to 'Yes'. (Like in the screenshot attached?)
Thanks for sticking with me while I try to get a better understating of how I can automatically hide unnecessary rows in a data source.
Q1: Data Source
A1: I always work with Data Source. Need to be? I dont know. However, I can say that when you have a data source, you can try out filters when viewing the data source. Rather handy.
Q2: Add column
A1. I have not seen an easy way to populate a new column. I would guess that you look for 'Yes', so a No or blank would be treated the same.
Q3: I dont understand. But the 'Use Formula' SS is example of restricting what you see in a choices object. Might want to try {{Invoice}} <> 'Yes', so that No or blank will show.
Q4: Hidden field is the object with the 'eye' and line in it.
I have attached a pretty complex form I have. It has multiple sources, writes out to a connected sql table with sql statements.
I find using List forms, and Screen forms to work out great.
1 person liker dette
@Robert, thank you so much for such a detailed response! As recommended, I ended up adding a Data Source field to the form. After several tries, I've realized that I had to filter rows by using fixed criteria and not a formula. (Screenshot attached) Once again, thanks a million; without your support, I wouldn't have been able to fix this issue.
I am still looking for a way to automate the data source. You've mentioned trying Yes in the dynamic field for the Invoice field. Can't seem to work. I wonder if there's a way to prefill the specific row (Invoice) in the data source with Yes, a few days after the form is submitted.