which table contains debtor specific transactions  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

which table contains debtor specific transactions

Postby JuiceyBrucey » Tue Feb 23, 2021 8:03 pm

Hi, I need to do a query that gets all debtor specific transactions so that I can create an API method that returns all, or filtered types, of transactions.
Which table would I get them from?
Cheers
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 132
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which table contains debtor specific transactions

Postby Mike.Sheen » Wed Feb 24, 2021 1:59 pm

JuiceyBrucey wrote:Hi, I need to do a query that gets all debtor specific transactions so that I can create an API method that returns all, or filtered types, of transactions.
Which table would I get them from?
Cheers


DB_Trans is the table which contains all debtor transactions. But I wouldn't do that yourself manually - we've done the heavy lifting for you and have AutoQuery setup - you just need to add the route in your own custom plugin:

Code: Select all
AppHost.Routes.Add(typeof(DB_TransQuery), "/Queries/DB_Trans", "GET", "Retrieves a list of debtor transactions.", "");


requests to this route can specify what value of any column to filter on, so the request can specify a specific DebtorID.

If you want greater control, or don't trust the requestor to look at transactions for any debtor, you can do that also - explicitly inject a filter value - the UserSettingsGETManyRequest in the standard REST API plugin is a good example.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: which table contains debtor specific transactions

Postby JuiceyBrucey » Thu Feb 25, 2021 8:56 am

hI Mike, Thank you very much.
I discovered this Queries section yesterday and it has made things so much easier. I am going to use it for a lot of queries.
This means that the only time I need a custom route is when I need to do a table join or something a little bit more complex than just querying one table.
Thank you very much. Cheers
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 132
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which table contains debtor specific transactions

Postby JuiceyBrucey » Thu Feb 25, 2021 9:04 am

Hi Mike,
I take it you are referring to below. I am not that familiar with C# and this is a crash course for me.
Is there any examples of this being implemented?
#region "Services"
[CompressResponse]
public class UserSettingServices : Service
{
[Authenticate]
public void Post(UserSettingPOSTRequest request)
{
JiwaApplication.Manager manager = this.SessionAs<JiwaAuthUserSession>().Manager;
manager.JLib.SetProfileKey(manager.Staff.RecID, request.Section, request.IDKey, request.Contents);
}
public IAutoQueryDb AutoQuery { get; set; }
[Authenticate]
public object Get(UserSettingsGETManyRequest query)
{
JiwaApplication.Manager manager = this.SessionAs<JiwaAuthUserSession>().Manager;
var q = AutoQuery.CreateQuery(query, base.Request).Where(String.Format("UserID = '{0}'", manager.Staff.RecID));
return AutoQuery.Execute(query, q);
}
}
#endregion

Cheers
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 132
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which table contains debtor specific transactions  Topic is solved

Postby Mike.Sheen » Thu Feb 25, 2021 10:56 am

JuiceyBrucey wrote:I take it you are referring to below. I am not that familiar with C# and this is a crash course for me.

Yes, that is what I was referring to.

JuiceyBrucey wrote:Is there any examples of this being implemented?

Ok, one of us must be missing something... because what you found and showed was an example of manually doing an AutoQuery with parameters injected.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 3 guests

cron