Showing what works HAS NOT been done yet

1

Hi.

I want all of my clients to have at least one article on all of the available Blog Sites

image
image1259×564 18.6 KB

I want to see be able to see which clients DO NOT HAVE an article yet on any given site.

I have tried a million ideas, so now I’m finally asking for directions,

Can anyone help?

2

Hello,

Would this sample do the trick? It uses set to make “diffs” between data.

3

That’s insanely good - thank you so much.
How did you achieve that? I can only see some of the formula

4

In fact, there are two key formulas:

  1. in Clients table, Blogs without articles:
set(Blogs.all) - set(
  Posts.lookupRecords(
    Clients=CONTAINS($id)
  ).Blog
)
  1. in Blogs table, Clients without articles (this one includes list comprehension to “flatten” the Clients column of the Posts table:
set(Clients.all) - set(
  client
  for clients in Posts.lookupRecords(Blog=$id).Clients
  for client in clients
)
5

So good! I am only just starting with formulas, so I really appreciate this!
Thanks so much @jperon

1 Like
6

Ideally, I will have a list of work completed and not completed. Is that easy to do?

7

In each formula, the content of the second set is the completed work: so you might get the “complementary” part. In that case, to avoid duplicate calculations, one column should refer to the other. So the definitions become:

  1. in Clients table:

    • Blogs with articles:
    Posts.lookupRecords(
  Clients=CONTAINS($id)
).Blog
    • Blogs without articles:
    set(Blogs.all) - set($Blogs_with_articles)

  2. in Blogs table:

    • Clients with articles:
    (
  client
  for clients in Posts.lookupRecords(Blog=$id).Clients
  for client in clients
)
    • Clients without articles:
    set(Clients.all) - set($Clients_with_articles)

I have updated the sample, and added permissions so that you might use it as a starting point for a new one by clicking on the share button at the top.

N. B. : The “Code View” at the bottom left gives a very useful and synthetic view of the logic of a document.

8

Amazing. Thank you

The Column Options Panel is greyed out so that I cannot see everything… Is it possible to free that up?

9

I’ve just added write permissions to all, so you should be able to work on this document nearly as if it were yours.

But by clicking on share, then Duplicate Document, you should be able to create your own copy.

10

You have been so helpful - I really appreciate it

12

@jperon Also, what if I wanted to only include blogs who have a “Use” toggle that is TRUE. That is, filtering for blogs that we are currently using and ignore one that we are not currently using.

Many thanks
Andrew

13

I’d use filtering, that’s one of the shining features of grist:

Capture vidéo du 2023-01-26 00-04-42

14

Why didn’t I think of that :smiley:

Just filter the “report”

15

So, I’d have to include the field (from the Blogs table) I want to filter on in the formula?
At the moment it only brings back the Client name

The reason I ask is that there are hundreds of blogs, of which we are only using <100

16

Sorry, I had forgotten about the formulas. It’s fixed, and moreover I added set(…) to the with_ columns to avoid duplicate names. To fix it, I replaced Blogs.all by Blogs.lookupRecords(Use=True) and so on, and added an if clause to list comprehensions.