json formatting – How to display past list items related to a column value?

In order to display a custom hover card, you need to use customCardProps element. See more details here. It has the following properties:

openOnEvent

  • hover – card appears on mouse hover;
  • click – card appears on click on the field.

directionalHint

  • topCenter bottomCenter leftCenter rightCenter
  • isBeakVisible – boolean. if true the card has a beak.

enter image description here

Sample JSON

{
    "elmType": "div",
    "txtContent": "@currentField.title",
    "customCardProps": {
        "openOnEvent": "hover",
        "directionalHint": "rightCenter",
        "isBeakVisible": true,
        "formatter": {
            "elmType": "div",
            "style":{ "display": "block", "font-size": "15px", "padding": "10px" },
            "children": [
                {
                    "elmType": "div",
                    "style": { "display": "block", "width":"100%", "text-align": "center", "padding": "10px" },
                      "children":[
                        {"elmType": "div", "txtContent":"Jour 1: 11-05-2021"},
                        {"elmType": "div", "txtContent":"Jour 2: 12-05-2021"},
                        {"elmType": "div", "txtContent":"Jour 3: 13-05-2021"},
                        {"elmType": "div", "txtContent":"Jour 4: 14-05-2021"}
                      ]
                   
                }
            ]
        }
    }
}

In your case, you will have to create a new multiline field that would include all this information. Then, you can create a Power Automate flow that is responsible for populating this new field automatically.

This way, your JSON formula will simply need to reference a single field to display correct content.

You can make your card as fancy as you want. For example:

enter image description here

How do I apply more than one conditional formatting rule using JSON to a SP Online List column?

You cannot apply multiple JSON formatting codes to single column. So, you have to manage multiple conditions/rules within same JSON.

For changing color to red when number is negative, add below code in your existing JSON:

...

"style": {
      "color": "=if(@currentField < 0, 'red', '#323130')"
}

...

Documentation: Use column formatting to customize SharePoint

sharepoint online – How do we vertically delete values from a single column?

Within a SharePoint Online list, there are options to either enter and exit out of Grid view to select and edit various parts of a list in a grid like fashion.

I see I can use my mouse to select, pull, and drag in order to select multiple row values from a single column.

My list example

Column A | Column B | Column C
value1
value2
value3
etc...

But, how do we bulk delete all values from a single column and still keep the column?

In my example above its, value1, value2 …. value1000.

sharepoint online – SPO: Using JSON to Show/Hide Modern Form Field Based on Value Selected in Lookup Column

I am trying to modify the new-entry form of a modern list in SharePoint Online by hiding or showing a field based on the value selected in a preceding field on the same form. Microsoft offers straightforward guidance on how to do this using a JSON snippet.

While they don’t list a lookup column as an unsupported field type to use for this purpose, they don’t provide guidance on how to use one in this way, either. And from my testing, you can’t simply grab the value of a lookup field in the described way:

=if(($LookupField)=='Hourly', 'true', 'false')

If I use a choice field instead of a lookup field, this works fine. But doing some digging, in other contexts it looks like the value may be captured in a .lookupValue property, so I tried it this way:

=if(($LookupField.lookupValue)=='Hourly', 'true', 'false')

Still no luck. Does anyone know if it is possible to use the value from a lookup field in this way? And if so, what do I need to do to make the magic happen?

What is best practice for referencing data rows within stored procedures – via PK, code column or data value?

Suppose you have a table for colours with columns:

  • id = automatically incrementing integer, primary key
  • code = short code reference for the colour, unique
  • colour = human-readable name of colour, unique

Example values might be:

Now imagine you have a stored procedure that, at some point, needs to reference this table. Let’s say the business logic says to obtain the colour “Green”. To achieve this, you could have any of the following three WHERE clauses:

  • WHERE id = 2
  • WHERE code = GR
  • WHERE colour = Green

Now, if the system is designed such that it is agreed that a code value, once created, never changes, then, in my view, that is the best column to reference because:

  • It is an alternate key
  • It is human-readable for people who maintain the code
  • It will not be impacted when the business decides to change the colour value to ‘Sea Green’

However, if a legacy table lacks such code values, what, in your opinion, is best practice? To reference the id column, or the colour column?

If you reference the id column, the code is not readable unless you then also add comments – you shouldn’t have to comment simple things like this. It sucks figuring out what statements like WHERE id not in (1, 7, 17, 24, 56) mean.

I’m not sure how often, in reality, the id value might change – but consider if you run a script during development to insert new colours but then delete those and insert some more. If your stored procedure references the id values from that last set of colours inserted but when you create your new colours in your next environment you skip the step that inserted the colours which ended up deleted, then the id values won’t match in that next environment. Bad practice, but it can happen – a developer develops their script on a dev instance not thinking that the id values will conflict with production (which, for example, may have had additional colours created manually by the business before your colour creation script runs).

If you reference the colour column, you run the risk that if the business does ask to update the description from ‘Green’ to ‘Sea Green’, that your procedure will begin to fail.

I suppose a further solution is to implement the code column when you need it, if it isn’t there already – probably the best solution?

json – Change Column Header Font Color on a Modern SharePoint List

I’ve been trying to hide a column header in a groupedby list on my Modern page. Since I haven’t been successful (can’t add a web part), I thought I might be able to format the column and add Json to change the color of the header to white. I haven’t been able to find anything that works to hide the column, so I figure this might be my best option. Problem is, I don’t know Json very well. Here’s an image of the list. I just have to either hide the Column Header “Question”, and the (1) number count. If I can’t hide them, I’d like to change their font color to white. It obviously works if I change it when I open developer tools, but I can’t save those changes.

Any help would be greatly appreciated.

enter image description here

Disable or hide checkboxes depending on value in another column in Google Sheets

Use conditional formatting to set the text color of checkboxes to white to make them invisible when the cell in column G in the same row is blank. An invisible checkbox cannot be toggled accidentally.

You can do this by applying this conditional formatting custom formula rule to the checkbox range F2:F:

=isblank(G2)

r – Summarizing a dataframe using a reference column

Consider the following dataframe:

df <- data.frame(waypoint = 1:10, pnt1 = NA, pnt2 = NA, pnt3 = NA)

x <- c("A", "B", "C", "D")

df$pnt1 <- as.factor(sample(x, 10, replace = T))
df$pnt2 <- as.factor(sample(x, 10, replace = T))
df$pnt3 <- as.factor(sample(x, 10, replace = T))

df

I’d like to summarize this dataframe in the following manner, where the values from the “pnt1, pnt2, and pnt3” columns in the original df are summed for each waypoint and placed into new columns “A, B, C, D”. The shell of the result would look like this:

df2 <- data.frame(waypoint = 1:10, A = NA, B = NA, C = NA, D = NA)

How can I code this to produce a result similar to df2 that is filled with the correct values?

postgresql – How do I add a where statement at the end of query for a column that does not have a table to reference from?

Use the WITH clause to get the result set and SELECT only the wanted rows.

WITH CTE As (
select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
) 
SELECT * FROM CTE WHERE accrual = 'YES'

Or use the SELECT query as SUBSECT in a FROM clause and use that

SELECT * FROM (
    select
    CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
            AND(coalesce(posted_date, paid_date) IS NULL
                OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual
    ) t1 
WHERE accrual = 'YES'

In both cases you need a valid SELECT

postgresql – How to find the counts of word occurrences in a column?

I have a column containing text in postgresql. Now I want to find, for a given input word, the counts of occurrences of the word in my text column.

So I have

  date_col   |  text_col
-------------+-----------------------
 2021-04-02  | This is a test.
-------------+---------------------
 2021-03-30  | A test is a test.
-------------+---------------------
 2021-03-30  | How to test?
-------------+---------------------
 2021-04-01  | One more test

and I want this result for the word 'test':

 count | num_occurrences
-------+-----------------
   3   |  1
-------+-----------------
   1   |  2

meaning 3 times there was excactly one occurrence of "test". Once there were two occurrences of "test" in the same row.

Later, I want to be able to query a given period with the same query.

My initial take was to create a new table with a row for every word like so:

  date_col  |  word 
------------+------------------
 2021-04-02 |  This
------------+------------------
 2021-04-02 |  is
------------+------------------
 2021-04-02 |  a

and do some counting and grouping. But is there a better way?