Database Mapping and TransactionType of order
Moderator: Phil Barton
Database Mapping and TransactionType of order
I have explore nves4 server database to find that which table has mapping for Marketing Representative checkbox of SoftPro but I do not find any database table which has mapping for these check boxes
And second, when I create any order in SoftPro so many times its TransactionType is 2 in [SelectDb].[pfm].[Order] Table so because of this order does not come to our second database.
And second, when I create any order in SoftPro so many times its TransactionType is 2 in [SelectDb].[pfm].[Order] Table so because of this order does not come to our second database.
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Database Mapping and TransactionType of order
There is a table that holds Marketing Source information. This SQL script should get you started.
I do not know anything about a second database. The order "Transaction type" is set in the user interface and maps to the values below. Please review your code or default settings to evaluate the setting.
Code: Select all
SELECT o.Number as 'Order Number', oit.Name, c.IsMarketingSource --, c.*
FROM [pfm].[Contact] c
JOIN [pf].[OrderItemType] oit
ON c.Type# = oit.ID
JOIN pf.[order] o
on c.RootId# = o.RootID
WHERE IsMarketingSource = 1
Code: Select all
public enum TransactionType
{
Purchase = 1,
Refinance = 2,
Equity = 3,
Other = 4
}
Bob Richards, Senior Software Developer, SoftPro
Re: Database Mapping and TransactionType of order
1)I want to know the mapping of checkbox of marketing reps in database either that marketing rep have any order or not in thier account. Please provide me which table column is storing this checkbox information.
2)The order "Transaction type" is set 2 in [SelectDb].[pfm].[Order] Table , I have searched that enum in code base we are not having such enum. Please connect with us or provide information about how to set default settings for Transactiontype in SoftPro
2)The order "Transaction type" is set 2 in [SelectDb].[pfm].[Order] Table , I have searched that enum in code base we are not having such enum. Please connect with us or provide information about how to set default settings for Transactiontype in SoftPro
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Database Mapping and TransactionType of order
Question 1: The marketing source flag was shown in my previous response showing the SQL query - [pfm].[Contact].IsMarketingSource
Question 2: The TransactionType enumeration documentation is provided in the Select SDK in the " SoftPro.OrderTracking.Client.Orders" namespace.
Question 2: The TransactionType enumeration documentation is provided in the Select SDK in the " SoftPro.OrderTracking.Client.Orders" namespace.
Code: Select all
using SoftPro.OrderTracking.Client.Orders;
order.TransactionType = TransactionType.Refinance
Bob Richards, Senior Software Developer, SoftPro
Re: Database Mapping and TransactionType of order
I want to know mapping of this check box of marketing reps which I showed in image
- Attachments
-
- This checkbox mapping I want to know
- markeRep.PNG (170.13 KiB) Viewed 7289 times
Re: Database Mapping and TransactionType of order
Please find the image of checkbox of marketing reps whose mapping I want
- Attachments
-
- markeRep.PNG (170.13 KiB) Viewed 7289 times
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Database Mapping and TransactionType of order
There is no direct mapping from the Marketing Representative position to Orders. What do you have to start with? Are you trying to determine which users are associated to orders? Are you starting with the user IDs?
Bob Richards, Senior Software Developer, SoftPro
Re: Database Mapping and TransactionType of order
I just want list of all marketing reps either they are having any order as marketing reps or not having .If someone is marked as marketing reps and currently he/she is not assign to any order as marketing reps so there is no data present in contact table for that marketing reps.
So in that case how could I know that someone is marketing reps or not.
There is a table SecurityUser in database which has all users of user tab whose image I send to you in that table there is no column for the checkboxes
Is there any table which is having mapping for that checkbox whose image I send you
If possible could you please connect with us between 8:00 AM IST to 9:30 PM IST and my email id is "tanisha.agnihotri@unthinkable.co"
So in that case how could I know that someone is marketing reps or not.
There is a table SecurityUser in database which has all users of user tab whose image I send to you in that table there is no column for the checkboxes
Is there any table which is having mapping for that checkbox whose image I send you
If possible could you please connect with us between 8:00 AM IST to 9:30 PM IST and my email id is "tanisha.agnihotri@unthinkable.co"
- Attachments
-
- markeRep.PNG (170.13 KiB) Viewed 7283 times
-
- Posts: 1382
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Database Mapping and TransactionType of order
This script will get all the users/groups that have the Marketing Representative position and display the number of orders for each one. Be aware that both the contact's "Marketing source" checkbox must be checked and the script only looks at the first "Marketing rep" user.
I leave it to you to add the additional qualification for when the order is closed. You will probably need to incorporate the [pfm].[Order] table into the logic that creates the #orders temp table to determine this information.
Code: Select all
-- Get marketing rep moniker value from DB.
declare @MarketingRepresentativeMoniker uniqueidentifier
= (SELECT ID FROM [core].[Position] WHERE Name like 'Marketing Representative')
-- Get list of all users/groups with "Marketing Rep" position selected.
DROP TABLE IF EXISTS #identities
CREATE TABLE #identities (
FullName varchar(256),
ID uniqueidentifier)
INSERT INTO #identities
SELECT si.FullName
,si.ID
FROM [core].[PositionAssociation] pa
JOIN [core].[SecurityIdentity] si
ON pa.Moniker = si.ID
where PositionID = @MarketingRepresentativeMoniker
-- Get count of all orders each Marketing Rep is associated with.
DROP TABLE IF EXISTS #orders
CREATE TABLE #orders (
ID uniqueidentifier,
NumOrders integer)
INSERT INTO #orders
SELECT MarketingRepID, count(*)
FROM [pf].[OrderInfo]
WHERE [MarketingRepID] IS NOT NULL
GROUP BY MarketingRepID
-- Display Market Rep name and number of orders.
SELECT FullName, NumOrders
FROM #identities i
LEFT JOIN #orders o
ON i.ID = o.ID
Bob Richards, Senior Software Developer, SoftPro
Re: Database Mapping and TransactionType of order
when I run "SELECT * FROM [NVES4].[SelectDb].[core].[PositionAssociation]
where PositionID = 'ECFABA37-FB08-4BA9-8250-4EF30ADECF72' " this query I got 74 rows .But in SoftPro we are having 9 marketing reps.
This query should return 9 rows not 74 rows.
where PositionID = 'ECFABA37-FB08-4BA9-8250-4EF30ADECF72' " this query I got 74 rows .But in SoftPro we are having 9 marketing reps.
This query should return 9 rows not 74 rows.
- Attachments
-
- I only want list of all users whose marketing reps checkbox is checked
- markeRep.PNG (170.13 KiB) Viewed 7261 times
-
- [core].[PositionAssociation] should return 9 rows when I applied where clause for marketing reps as there are 9 market reps
- mrdropdown.PNG (26.22 KiB) Viewed 7261 times