Can the selection of a lookup field be enforced?

I’m struggling with a lookup column in a list.

For different reasons I need users to choose a value for a new or edited list item in a lookup column. However as far as I can see, there is no possibility to enforce content in a lookup field on creating a new list item. Also lookup colums aren’t accessible for column validation formulas, are they?

Of course I can let a Flow check that, but I’ve got too many flows operating on those lists already so I want to try and do everything what can be done without. Any ideas?

sql server – Speed up Key Lookup (clustered)

I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%.

Is there anyway I can speed this query up?

Execution plan: https://www.brentozar.com/pastetheplan/?id=r1oIcWofd

exec sp_executesql 
N'
SELECT TOP(@__p_4) 
    (v).(County), 
    (v).(Community), 
    (v).(DataProvider), 
    (v).(Site), 
    (v).(Latitude), 
    (v).(Longitude), 
    (v).(Elevation), 
    (v).(Instrument), 
    (v).(MonitorId), 
    (v).(ParameterId), 
    (v).(Parameter), 
    (v).(MeasurementStartTime), 
    (v).(MeasuredValue), 
    (v).(AdjustedValue), 
    (v).(Units), 
    (v).(AdjustmentDesc), 
    (v).(LowerLimitCheck), 
    (v).(UpperLimitCheck), 
    (v).(RepeatingValueCheck), 
    (v).(SpikeCheck), 
    (v).(QualityPreliminary)
FROM (vw_DownloadDataSubhourly) AS (v)
WHERE 
(
    (((v).(CountyId) = @__Parse_0) AND ((v).(MeasurementStartTime) >= @__start_1)) 
    AND ((v).(MeasurementStartTime) <= @__end_2)
) 
AND (v).(ParameterType) IN (''PM2.5'')
ORDER BY (v).(Site), (v).(MonitorId), (v).(ParameterId), (v).(MeasurementStartTime)
',
N'@__p_4 int,@__Parse_0 int,@__start_1 datetime,@__end_2 datetime',
@__p_4=750000,@__Parse_0=34,@__start_1='2019-10-01 00:00:00',@__end_2='2020-12-31 23:59:59'

vw_DownloadDataSubhourly
Execution plan: https://www.brentozar.com/PasteThePlan/?id=BkiX1Vsz_


CREATE view (dbo).(vw_DownloadDataSubhourly) as

SELECT      
                DataProvider.DataProviderId, 
                County.CountyId, 
                Community.CommunityId, 
                County.CountyName as "County",
                Community.CommunityName as "Community",
                DataProvider.DataProviderShortName as "DataProvider", 
                Site.SiteLongName as "Site",
                SiteConfig.LatitudeTrunc as "Latitude",
                SiteConfig.LongitudeTrunc as "Longitude",
                SiteConfig.Elevation,
                Monitor.ExternalMonitorId as "MonitorId",
                Parameter.ParameterId as "ParameterId",
                Parameter.ParameterNamePublic "Parameter",
                Parameter.ParameterType "ParameterType",
                SubHourlyMeasurementData.StartDateTime "MeasurementStartTime",
                SubHourlyMeasurementData.AObs as "MeasuredValue",
                SubHourlyMeasurementData.AObsAdj as "AdjustedValue",
                UOM.UOMAbbreviation as "Units",
                AdjCode.AdjCodeDescription as "AdjustmentDesc",
                concat(MonitorConfig.MonitorMfr, ' ', MonitorConfig.MonitorModel) as "Instrument",
                case 
                    when SubHourlyMeasurementData.QF06 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF06 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF06 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF06 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF06 = '1' then 'Fail'
                    else NULL
                    end as "LowerLimitCheck",
                case 
                    when SubHourlyMeasurementData.QF05 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF05 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF05 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF05 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF05 = '1' then 'Fail'
                    else NULL
                    end as "UpperLimitCheck",
                case 
                    when SubHourlyMeasurementData.QF04 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF04 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF04 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF04 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF04 = '1' then 'Fail'
                    else NULL
                    end as "RepeatingValueCheck",
                case 
                    when SubHourlyMeasurementData.QF07 = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QF07 = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QF07 = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QF07 = '0' then 'Pass'
                    when SubHourlyMeasurementData.QF07 = '1' then 'Fail'
                    else NULL
                    end as "SpikeCheck",
                case 
                    when SubHourlyMeasurementData.QOverall = '-3' then 'QC not computed'
                    when SubHourlyMeasurementData.QOverall = '-2' then 'Not enough data'
                    when SubHourlyMeasurementData.QOverall = '-1' then 'Not enough site/monitor info'
                    when SubHourlyMeasurementData.QOverall = '0' then 'Passed all checks'
                    when SubHourlyMeasurementData.QOverall = '1' then 'Suspect'
                    else NULL
                    end as "QualityPreliminary"

FROM           SubHourlyMeasurementData 
                         INNER JOIN
                         DataProvider ON SubHourlyMeasurementData.DataProviderId = DataProvider.DataProviderId 
                         INNER JOIN
                         Site ON SubHourlyMeasurementData.SiteId = Site.SiteId AND Site.DataProviderId = DataProvider.DataProviderId
                         LEFT JOIN Community ON Site.CommunityId = Community.CommunityId
                         INNER JOIN 
                         SiteConfig ON SiteConfig.SiteId = SubHourlyMeasurementData.SiteId
                         INNER JOIN
                         County ON County.CountyId = SiteConfig.CountyId
                         INNER JOIN
                         Monitor ON Monitor.MonitorId = SubHourlyMeasurementData.MonitorId
                         INNER JOIN
                         MonitorConfig ON MonitorConfig.MonitorConfigId = SubHourlyMeasurementData.MonitorConfigId
                         INNER JOIN
                         Parameter ON Parameter.ParameterId = SubHourlyMeasurementData.ParameterId
                         INNER JOIN
                         UOM ON SubHourlyMeasurementData.UOMId = UOM.UOMId
                         LEFT JOIN
                         AdjCode ON SubHourlyMeasurementData.AdjCodeId = AdjCode.AdjCodeId
                         INNER JOIN
                         StreamSegment ON SubHourlyMeasurementData.StreamSegmentId = StreamSegment.StreamSegmentId
                         INNER JOIN 
                         MeasurementTechnique ON MeasurementTechnique.MeasurementTechniqueId = StreamSegment.MeasurementTechniqueId
                         AND MeasurementTechnique.ParameterId = SubHourlyMeasurementData.ParameterId
                         INNER JOIN
                         SamplingDuration ON SamplingDuration.SamplingDurationId = StreamSegment.SamplingDurationId
                         INNER JOIN
                         SamplingFrequencyUnits ON SamplingFrequencyUnits.SamplingFrequencyUnitsId = StreamSegment.SamplingFrequencyUnitsId

WHERE SubHourlyMeasurementData.StartDateTime >= MonitorConfig.StartDateTime and StreamSegment.IsDownloadable = 1 and SubHourlyMeasurementData.iscalculated = 1 

GO

Dovecot+Postfix: userdb lookup: user noreply@example.com doesn’t exist

I am trying to send email from my local computer over smtp on my remote server. I have installed postfix+dovecot and use virtual users for authentication over login mechanism. I have created new virtual user with doveadm user command. All my users is stored in passwd-file:

passdb {
  driver = passwd-file
  args = scheme=CRYPT username_format=%n /etc/dovecot/users
}

userdb {
  driver = passwd-file
  args = username_format=%n /etc/dovecot/users

  default_fields = uid=vmail gid=vmail home=/home/vmail/%d/%u
}

When I run $sudo doveadm user noreply@example.com I get:

userdb lookup: user noreply@example.com doesn’t exist

but when I run $sudo doveadm user *noreply@example.com (wildcard) I get response with my emails:

no-reply@example.com

noreply@example.com

Where is the problem?

I was starting with this tutorial https://help.ubuntu.com/community/PostfixVirtualMailBoxClamSmtpHowto but additionally I added ssl.

Here is my users file:

no-reply@example.com:{CRYPT}mypasshash:5000:5000::/home/vmail/example.com/no-reply/:/bin/false::
noreply@example.com:{CRYPT}mypasshash:5000:5000::/home/vmail/example.com/noreply/:/bin/false::

Notes don’t show in Guest Order Lookup page

My site has the ability for logged-in customers to see orders in their Order History page and also a Guest Lookup where they can lookup an existing order putting in order number and last name.

The two reports are identical with one exception: Order Notes that we put in with “Visible On Storefront” checked will show up if you’re looking at the order while logged in, but they do not show up when looking at the Guest Lookup page. I would like to change that so that the notes do show, but so far I’m having trouble tracking down where to make that change.

How could I change that setting that hides comments / notes while looking at the order through Guest view?

privacy – Why is cell phone reverse lookup still so impoverished?

Often you get a call from a non-business number and your phone’s filter app doesn’t know who it might be. You might Google it, and find a thousand sites promising to tell you its owner, but they only have placeholders.

This is kind of surprising to me. With the amount of data leaking and unconscionable data sharing with third parties by half the sites you give your number to, doesn’t it seem likely that a large proportion of numbers would have names attached to them?

Don’t get me wrong — I’m not advocating for this kind of loss of privacy. I’m just curious how it is that the number-owner mapping has remained relatively obscure. I’m probably missing something obvious.

Creating a Flow vs. Using Lookup Columns in SP List

I have two lists MemberList and MemberCertsandQualsList. Both lists share several common columns:

Location Alpha

Location Name and No.

Location Region

Location County, etc…

I have a Patch in Power Apps that updates these fields in the MemberList. The update only has to change a single line of data in the MemberList, as each UnqID only appears once on that list.

I also need these same exact fields updated in the MemberCertsandQualsList every time they are changed/updated the MemberList. The process for updating the MemberCertsandQualsList may have to update multiple line items of the same information base on the UnqID depending on the number of certs or quals the member has in the system. The UnqID may appear several times in the MemberCertsandQualsList; consequently, the entire list (more than 5,000 items) would need to be check every time a members data is updated in the MemberList.

It is more prudent or of greater efficiency to use a Lookup (if such a thing is even possible) to pull the common fields into the

MemberCertsandQualsList, or to use a Flow to update the MemberCertsandQualsList every time there is an update of change to the MemberList?

lookup column – How to compare dates in SharePoint List “A” to another Date in List “B”

So, I would like to Compare if any of my Dates from List “A” is between my StartDate and EndDate from List “B”. I am new to sharepoint and any Article can show step by step would be greatly appreciated.

LIST “A”

Holiday Dates
1 05/12/2021
2 07/04/2021

LIST “B”
| STARTDATE | Dates | YES/NO (CALCULATED FIELD)
| ——— | ————– | ——-
| 05/01/2021| 05/18/2021 | YES
| 06/01/2021| 07/03/2021 | NO

need a calculated field like below:

YES/NO (CALCULATED FIELD)=  IF LISTA.DATES BETWEEN LISTB.DATE THEN YES ELSE NO

SPD 2013. Is it possible to send a email that use’s a linked lookup list

I have a workflow running that looks at a Resource List A. When new starters arrive it sends the email which includes a Location field which is a lookup from List B. Within List A i also display Country and Region that are also pulled in from List B via the main link/lookup field Location.

I would like to also include the country field from List B in the email, but it doesn’t appear as a field.
Any ideas how i can include this without creating it as another direct lookup?
Thanks

sharepoint designer – Lookup field to same list not available in SPD workflow?

I have a list called “Requests”. This list has a text field called REQ-ID that is an “ID” field that corresponds exactly with the built in ID field (a workflow on item created populates the REQ-ID field with "REQ-(%CurrentItem:ID%)").

Through whatever business process, these requests get approved.

Now the users want a way to create a new “request” that is really an update to a previously approved request, and the approvers want to know the REQ-ID of the previously approved request.

Easy – add a lookup field (named Previously Approved Request) that refers back to the same “Requests” list it’s on, and have it show the REQ-ID field.

Done. Shows up on forms, when users are creating new requests, if it’s an update of an old one they can choose the REQ-ID of the old one to link it back. No issues there.

However, now I want to add a little message to the email that goes out alerting the approvers that this new request they are getting notified about happens to be an update to a previously approved request, and include the REQ-ID from the looked up item in the Previously Approved Request field. But I can’t because the Previously Approved Request field does not show up when I try to do a workflow lookup to (%CurrentItem:<FieldName>%). The Previously Approved Request field does not show up in the list of available fields to get from the current item.

This is from a list workflow (item created) on the “Request” list, the same list that the column is on and that the column is a lookup to.

I have gone into the list itself through the SPD interface, and can confirm that SPD sees the column on the list (with it’s correct settings), and sees the column in the default Item list content type (in the correct position, etc.). I even tried clearing my SPD cache to force it to reload everything about the site. Same thing, still sees it on the list, but it just isn’t there when I want to access it from a workflow.

Why? Where did it go?

docker swarm – Merge few files in Ansible lookup file

So far I use simple code to deploy Docker stacks. I don’t want to use community.general.docker_stack module, due to its ridiculous requirement: the stack definition (docker compose yml file) needs to be placed at the remote node, while I want to have them all gathered at local manager host. Example code is below (part of role/tasks and vars):

- name: deploy Docker stacks
  command: docker stack deploy --compose-file - {{ item.name }}
  args:
    stdin: "{{ lookup('file', item.source) }}"
  with_items: "{{ docker_swarm_stacks }}"
  when: (docker_swarm_stacks is defined)
docker_swarm_stacks:
  - name: portainer
    source: files/docker_swarm/stacks/portainer.yml
  - name: www-1
    source: files/docker_swarm/stacks/nginx-static.yml
  - name: www-2
    source: files/docker_swarm/stacks/wordpress.yml

So far, so good. With appropriate compose files (placed files/docker_swarm/stacks) in, it works very nice. One stack is created from one yml file (of course one yml file can be used to create similar stacks with different names).
But I would like to improve it and here is a great issue:
I would like to make it possible to use (merge) many compose files as definitions for 1 stack, but the “{{lookup(‘file’, ‘path/to/file.txt’)}}” is limited to 1 file. I’ve already tried to use list of files, but it fails.
Now I don’t have a better idea how to solve it, so I would appreciate if you can share your knowledge and maybe someone has an idea how to solve it.