Creating a relational Databse with future Workflows

[Steve_Stifler]

Hi guys and girls

Firstly, I can't believe how many posts I have read in your forums, with actual answers......"Proper" actual answers....that answer the question someone had asked. I'm not sure if you guys venture far outside of this forum, but you're not missing anything....It's restored my faith in posting on forums....so I'm looking forward to this experience.

OK, where do I start......I have a hybrid "Property Development / Financial Planning / Project Management" business model, which requires:

a) A database that can handle complex calculations, fetching data from many different tables for one calculation as well as handling many to many type relationships

b) A Customised Project Management system that uses the same tables as the database in a) with the same functionality as MS Project (I've tried them all and nothing tops MSP)

c) The ability to use emails (Sent and received) as triggers to trigger workflows

d) The ability to "Read" the data contained in an email and trigger events accordingly

d) Something I can build and customise myself and

e) Everything needs to be self hosted

Now, as you can probably gather from my elocution, I'm no IT guru but I am a problem solver. I need complete control over what is displayed, how it is entered and the website itself as the database (I say "database" but the correct term is more than likely "CRM"), The PM system, the reporting tools, the memebrship platform, the emailing system and the website will all be working with the same data....So after 18 mths of looking for a system, I saw Processwire and with my limited knowledge, I thought it looked like the way to go finally. So I have installed it on my CPanel, done the "Hello World" tutorial then wanted to know what direction I take to statr building this Monolith.

I have read that there are "Pages with API" or "Tables" and I believe I need to decide as you can't switch easily from one to the other if you change your mind. All I'll need is a little steer in teh right direction, I don't expect someone to provide me the solution I just need someone to point me in the direction.

Thanks Legends.

Jeremy Allen.

[bernhard]

Hi Jeremy and welcome to the forum ? 

1 hour ago, Steve_Stifler said:

Firstly, I can't believe how many posts I have read in your forums, with actual answers......"Proper" actual answers....that answer the question someone had asked. I'm not sure if you guys venture far outside of this forum, but you're not missing anything....It's restored my faith in posting on forums....so I'm looking forward to this experience.

I know that feeling, coming from a terrible one ? 

1 hour ago, Steve_Stifler said:

I need complete control over what is displayed, how it is entered and the website itself as the database (I say "database" but the correct term is more than likely "CRM")

Sounds like a lot of work ? And I know what I'm talking about:

ProcessWire is great for building custom forms for whatever "datatype" you need (regular page edits) and for building relations between those pages. It is IMHO not so great for displaying this data in a user-friendly way. We have listers and we have MarkupAdminDataTable, but both are not ideal for a little more complex tabular data (and I guess that's what you'll need a lot for such a system). That's why I built RockFinder, RockGrid and recently also RockCharts (early stage, not released yet). All of them are not totally easy to use for non-devs.

You'll also need to learn how to create custom admin pages, but that's easy ?  

And finally, you'll need to grasp the concept of hooks: https://processwire.com/blog/posts/new-ajax-driven-inputs-conditional-hooks-template-family-settings-and-more/#new-conditional-hooks

To make everything maintainable (and maybe also reusable), you'll need to pack everything in a module: https://processwire.com/docs/modules/

Good luck and have fun ? 

 

[Steve_Stifler]

41 minutes ago, bernhard said:

Hi Jeremy and welcome to the forum ? 

I know that feeling, coming from a terrible one ? 

Sounds like a lot of work ? And I know what I'm talking about:

ProcessWire is great for building custom forms for whatever "datatype" you need (regular page edits) and for building relations between those pages. It is IMHO not so great for displaying this data in a user-friendly way. We have listers and we have MarkupAdminDataTable, but both are not ideal for a little more complex tabular data (and I guess that's what you'll need a lot for such a system). That's why I built RockFinder, RockGrid and recently also RockCharts (early stage, not released yet). All of them are not totally easy to use for non-devs.

You'll also need to learn how to create custom admin pages, but that's easy ?  

And finally, you'll need to grasp the concept of hooks: https://processwire.com/blog/posts/new-ajax-driven-inputs-conditional-hooks-template-family-settings-and-more/#new-conditional-hooks

To make everything maintainable (and maybe also reusable), you'll need to pack everything in a module: https://processwire.com/docs/modules/

Good luck and have fun ? 

 

......and within a 2hr window I get a response.....So I'm gathering you've had a thing or two to do with finance type systems Bernhard. I currently have a WP site which my pretty pictures are hosted on (investorhq.com.au) but find it VERY inflexible for placement of anything you want to put on the screen, always having to work within theme that for some reason I can't work out (and even including the "Blank" themes) want to include a sidebar, and some text which I can't get rid of.....anyway, I haven't delved into how to create a front end yet for PW but am I going to experience the same here? I intended to not use tables for reporting and make it more pretty pictures and other distractions.

[psy]

Hi @Steve_Stifler and welcome to ProcessWire

Forget the WP model of themes etc that force you into their on-screen boxes. With ProcessWire you have complete control over what goes where & how on the frontend. You design your frontend layout and retrieve the data you need to populate the page in the templates

[Steve_Stifler]

....everyone is so nice here.

Hey Psy

That was the impression I got by going through all those CMSes.

Guys and Girls, a MASSIVE "Thank you" for your (Prompt) advice here, between Bernhard's links and that icing on the cake by Psy, I've gotten further and feel more confident in the past 6 hours than I have with other help forums in the last 2 years....and everything was relevant.

JA.

 

[Steve_Stifler]

....Oh, one other thing, to confirm I've got it right in my head as to how to attack this, I'm gathering I create a page/form per database "Table" or "Group of columns" in the back end and they then are completed on the front end when the data needs to be entered. Am I in essence creating a Page Template and each record is a different Page, or am I using the 1 Page and the data is being stored in a database somewhere?

JA

[thetuningspoon]

4 minutes ago, Steve_Stifler said:

....Oh, one other thing, to confirm I've got it right in my head as to how to attack this, I'm gathering I create a page/form per database "Table" or "Group of columns" in the back end and they then are completed on the front end when the data needs to be entered. Am I in essence creating a Page Template and each record is a different Page, or am I using the 1 Page and the data is being stored in a database somewhere?

JA

That is correct. A page can be thought of as a particular record and the template as the table/schema.

But if you are creating a front-end form for populating data to pages, you may want to create a single page with its own template which has the form on it for creating the records, and then have a hidden area in the page tree where the data-only pages are kept (this is in essence how PW's admin page editor works). The possibilities are endless.  But either way, you're just working with pages--no outside database. 

[Steve_Stifler]

2 hours ago, thetuningspoon said:

That is correct. A page can be thought of as a particular record and the template as the table/schema.

But if you are creating a front-end form for populating data to pages, you may want to create a single page with its own template which has the form on it for creating the records, and then have a hidden area in the page tree where the data-only pages are kept (this is in essence how PW's admin page editor works). The possibilities are endless.  But either way, you're just working with pages--no outside database. 

Thanks TTS...that clears it all up, I actually get what you mean.

Guys and Girls, on a side note, I think you need to look at licencing the system you have in place for forums or online help....no shit, I've been on a LOT of them and the quickest I've had (Even being intermediate on some) is 4 times longer than I've experienced here....not to mention all answers have been relevant to my question.

Anyway, love you guys....you rock! If ever you're looking at investing in Australian property, let me know and I'll make sure you get looked after!

JA.

[Autofahrn]

3 hours ago, thetuningspoon said:

A page can be thought of as a particular record and the template as the table/schema.

@Steve_Stifler, if you are thinking database-wise, this probably needs some refinement.

The database behind PW indeed has a table "Pages" which holds vital information about a particular page, which includes its unique identifier, the identifier of its parent page, the assigned template and a name, to name a few, but no real data. It primarily defines the pages location within the page tree.

Data, in contrast, is stored in individual tables which directly relate to a particular field. One row of such a field table contains "the value" (like a string, integer etc.) and the id of the page, to which this particular row belongs to. For this reason a particular field can only exist once on a page (you may, of course, have multiple fields using the same fieldtype assigned to the same page).

So, if a page hosts 10 fields, then accessing the data of these 10 fields requires access to 10 tables of the database (which is cached in PW, so you do not need to bother with that).

On the other hand, with PW it is rather simple to create own fieldtypes which refer to multiple database columns. So, if your standard row of data consists of 10 values, you may create an inputfield holding exactly these 10 values, which are stored in a single row of a single table. As long as you can define your data from the existing inputfield types, creating own fieldtypes is really a no-brainer.

[Steve_Stifler]

36 minutes ago, Autofahrn said:

@Steve_Stifler, if you are thinking database-wise, this probably needs some refinement.

The database behind PW indeed has a table "Pages" which holds vital information about a particular page, which includes its unique identifier, the identifier of its parent page, the assigned template and a name, to name a few, but no real data. It primarily defines the pages location within the page tree.

Data, in contrast, is stored in individual tables which directly relate to a particular field. One row of such a field table contains "the value" (like a string, integer etc.) and the id of the page, to which this particular row belongs to. For this reason a particular field can only exist once on a page (you may, of course, have multiple fields using the same fieldtype assigned to the same page).

So, if a page hosts 10 fields, then accessing the data of these 10 fields requires access to 10 tables of the database (which is cached in PW, so you do not need to bother with that).

On the other hand, with PW it is rather simple to create own fieldtypes which refer to multiple database columns. So, if your standard row of data consists of 10 values, you may create an inputfield holding exactly these 10 values, which are stored in a single row of a single table. As long as you can define your data from the existing inputfield types, creating own fieldtypes is really a no-brainer.

OK. I was asking for the purpose of when I go to create my formulas, I am going to need to know how the system thinks to know how to structure my "Fetch" statements ro however I grab data from another Table's/Page's field. So taking what TTS said and from what you are saying (and please excuse the terminology but I think you guys are smart enough to translate into PW as I'm not yet!)

I have a Table/Page called "PORTFOLIOS" which houses the Assets and Liability related fields of a client and another called "CASHFLOWS" which houses the clients income and expenditure related fields. CASHFLOWS will need to fetch the "Debt" field from Portfolios to calculate the repayments and PORTFOLIOS will need to fetch the "Salary" field from cashflow and the "Income Items" to determine what is an asset or not. So in this case, for a particular formula which calculates REPAYMENTS am I (in English of course) scripting in CASHFLOWS

- Define x as a variable which equals the following

     * the value of the DEBT field in the table/page PORTFOLIOS

- REPAYMENTS = x

So I am fetching by telling the system which table/page to go to then the field_name?.....am I on the right track?

Edited February 21, 2019 by Steve_Stifler
Missed part f the formula...it may mean nothing but then again, it may not.