Database Mapping and TransactionType of order

Discussions concerning general integration topics.

Moderator: Phil Barton

ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Database Mapping and TransactionType of order

Post by ngillet »

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.
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Database Mapping and TransactionType of order

Post by BobRichards »

There is a table that holds Marketing Source information. This SQL script should get you started.

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
  
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

public enum TransactionType
{
    Purchase = 1,
    Refinance = 2,
    Equity = 3,
    Other = 4
}
Bob Richards, Senior Software Developer, SoftPro
ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Re: Database Mapping and TransactionType of order

Post by ngillet »

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
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Database Mapping and TransactionType of order

Post by BobRichards »

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.

Code: Select all

using SoftPro.OrderTracking.Client.Orders;

order.TransactionType = TransactionType.Refinance
TransactionTypeUI.png
TransactionTypeUI.png (25.73 KiB) Viewed 6809 times
Bob Richards, Senior Software Developer, SoftPro
ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Re: Database Mapping and TransactionType of order

Post by ngillet »

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
This checkbox mapping I want to know
markeRep.PNG (170.13 KiB) Viewed 6805 times
ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Re: Database Mapping and TransactionType of order

Post by ngillet »

Please find the image of checkbox of marketing reps whose mapping I want
Attachments
markeRep.PNG
markeRep.PNG (170.13 KiB) Viewed 6805 times
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Database Mapping and TransactionType of order

Post by BobRichards »

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
ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Re: Database Mapping and TransactionType of order

Post by ngillet »

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"
Attachments
markeRep.PNG
markeRep.PNG (170.13 KiB) Viewed 6799 times
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Database Mapping and TransactionType of order

Post by BobRichards »

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.
2022-03-17_10-45-21.png
2022-03-17_10-45-21.png (7.86 KiB) Viewed 6795 times

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
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.
Bob Richards, Senior Software Developer, SoftPro
ngillet
Posts: 40
Joined: Tue Apr 05, 2016 4:32 pm

Re: Database Mapping and TransactionType of order

Post by ngillet »

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.
Attachments
I only want list of all users whose marketing reps checkbox is checked
I only want list of all users whose marketing reps checkbox is checked
markeRep.PNG (170.13 KiB) Viewed 6777 times
[core].[PositionAssociation] should return 9 rows when I applied where clause for marketing reps as there are 9 market reps
[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 6777 times
Post Reply