Database with periodic import of changing data possible?

[HarryPhone]

Hi,

I’m a newbie with Processwire and I want to ask you if Processwire is the right software to solve our problem:

 

We need a database for our sales representatives:

• They should document their work with a daily report. So they have to fill out a form.
• The sales representatives visit different customers every day and they talk about different products (one or more) with every customer.
• If they have time, they should start to fill out the form. Later they should be able to continue.

 

Export and import as a periodic process:

• There is a leading system with data like customer data (name, address,…) or name and ID of the sales representatives.
• We want to export the data (CSV) and import them in Processwire. But there will be updates of the data in the leading system, so we want to update (or delete and import new) this data. But the linking of the data to the Processwire-fields may not be lost.

 

The daily report contains fields like this:

Part 1: Sales representative / Day

• The name of the sales representative (he should pick his name or ID out of a drop down list).
• The type of the report (Drop Down)
• Some fields with dates and time.

Part 2: Customer

• Customer-ID or name (perfect would be an AJAX-Search) – after pressing enter, there should be automatic loaded other customers data like address
• Select boxes
• Comments about the conversation

Part 3: Products

• Product
• Actions (Select boxes)
• Comments
• Attachements (Upload)

Summary:

• The sales representative visits several customers and talks with them about several products.
• Front-page-editing would be great

 

My questions:

1. Will this work with Processwire?
2. Can I solve the daily report requirements (Part 1-3) with one big form and with repeater fields?
3. Which modules do I need for this project? Input, Lists,... (Note: I’m not a good programmer and the money for Pro-Modules ist available).
4. Access rights: Is this possible: Every sales representative can only see and edit his own daily reports?

 

Perhaps there is someone who can give at least the answer for question 1 and how to go on. Otherwise my bosses will decide to take a commercial solution which I will not prefer because it is far away from the power of Processwire (and it runs on Windows. In my opinion a no go for an internet solution).

Many thanks for your help!

Best regards,

Harry

[arjen]

Absolutely. Nowadays we build most of the stuff in the back-end with custom Process modules in combined with the forms api. Importing content is also pretty easy. You might ask why not in the front-end? Because the roles and permissions work flawlessly in the backend and the back-end looks pretty good (Reno looks imo the best), but you can create your own based on Uikit. If you want to overwrite the default styling take a look at AdminCustomFiles where you load css and javascript files. Also take a look at MarkupAdminDataTable since most of the stuff you will need is forms and tables.

This makes it pretty easy to prototype. 

[HarryPhone]

Thank you for your answer. Now I know a little bit more.

How can I import and manage my data?

• I've got a table with fields like "Customer-ID, name, country" and so on.
• I have to import the data periodically because there will be updates (e.g. a customer change his residence).

How can I solve this?

Perhaps I can use fieldtypes like Matrix or Profield: Table for that? I think, if I can save all data in one database-table, the update would be much more easier...

Which modules are recommend for my project?

For example I think Formbuilder would be more flexible than the forms.api especially I need input indepencies.

[arjen]

Hi @HarryPhone,

Based on what I know now: there are no specific modules you need.

I would use pages to store the data. You can read the csv file and use the the API to save the data. See this comment on how to create a bash file. Make sure the the new file is somewhere accessible over the internet of place the csv file in a folder. Then you can create a cronjob and trigger the bash script to periodically execute the script.

InputDependencies will work with the forms api as well:

$field = $modules->get('InputfieldSelect');
$field->showIf = "something=123";

 

[dragan]

Quote

 

I have to import the data periodically because there will be updates (e.g. a customer change his residence).

 

Importing data into PW is certainly no big deal. I guess the actual challenge is to find out whether an update is necessary, or creating a new recordset (page).

If your export from the other system (I guess a CRM) only allows to have ALL data exported, then it's going to be tricky. It would be ideal if you had three different exports: 

• only completely new records (new customers, new sales reps)
• only updates to existing data (new customer address, new sales rep phone number)
• only removals from the system (or a flag like "inactive")

You would probably need unique identifiers (perhaps an ID from your CRM that you store as a special field in your PW-template), to make sure you update the right page.

Edited July 8, 2017 by dragan
context

[HarryPhone]

Hello,

thanks for your answers. There were very useful things. Thanks a lot.

Yes, my problem is this way: The leading system allows only to export ALL data. And I don't have an unique identifier for new, updates and inactive. 

That's why I'm searching for the perfect fieldtype. While testing I always have a look into MySQL with PHPMyadmin. I want to see the data structure. If direct import over PHPMydmin would work - yes, this would be great. But I've seen, with the normal fieldtypes this is not possible.

• EVENTS fieldtyp looks good but it's only an example. 
• MATRIX could be perfect but I'm not able to set up this fieldtyp correctly. I always get an error.
• That's why I'll buy the Profields and hope fieldtype TABLE can solve this. Ryan wrote, the data is stored directly in the MySQL database. I will see. 

But I don't know if it is possible to store 10.000 rows of entries to one page.

 

[kongondo]

7 hours ago, HarryPhone said:

But I don't know if it is possible to store 10.000 rows of entries to one page.

Technically, nothing is stored in a page, but a field. 10,000 rows of entries in one field is nothing really. MySQL can easily handle millions of rows. The crucial question is usually how you are going to query those rows in a fast and efficient manner. One can't and shouldn't query all the rows. Instead, it usually boils down to using a combination of limit (number of queried rows) and caching.

Welcome to the forums .

Edited July 10, 2017 by kongondo

[bernhard]

Hi HarryPhone,

sounds like you are just a little too early. Your usecase looks like a perfect fit for my new modules

On 4.7.2017 at 8:42 PM, arjen said:

Absolutely. Nowadays we build most of the stuff in the back-end with custom Process modules in combined with the forms api. Importing content is also pretty easy. You might ask why not in the front-end? Because the roles and permissions work flawlessly in the backend and the back-end looks pretty good (Reno looks imo the best), but you can create your own based on Uikit. If you want to overwrite the default styling take a look at AdminCustomFiles where you load css and javascript files. Also take a look at MarkupAdminDataTable since most of the stuff you will need is forms and tables.

This makes it pretty easy to prototype. 

agree with this, would do it a little different though:

i would use my RockDatatables instead of the Core AdminDataTable. you will be much quicker and you will have much more options regarding rendering the table (coloring cells, rendering linked icons and the like).

regarding the import you have serveral options (there are several csv modules around). it's also very easy to write your own XML importer:

using my module you can build highly customized listers. its also very easy to create processmodules to make them available as single pages in the pw admin.

the module is not released yet and has some drawbacks (like mobile usage or handling large datasets is not yet implemented, that would be a case for regular listers), but maybe it's interesting to know that something like this is coming up.