Custom profile field with value from excel

Looking for an Extension? Have an Extension request? Post your request here for help. (Note: This forum is community supported; while there is an Extensions Development Team, said team does not dedicate itself to handling requests in this forum)
Get Involved
Post Reply
Lord Phobos
Registered User
Posts: 613
Joined: Tue May 18, 2004 11:41 pm

Custom profile field with value from excel

Post by Lord Phobos » Mon Oct 15, 2018 10:36 pm

Hi

I run a very long tournament of a great number of board games with many players in my association.
I assign each player points based on their plays and their victories.
All of this is managed by a very complex excel file.

What I'm asking for now (as I have more ambitious plans in my mind) is not to manage all this stuff via phpbb and sql tables, altough it would be very cool (contact me in pm if you want details, we can discuss the payment).

I created a custom field named "Alias" in on my board.

Would it be possibile to automatically populate another field (named "score") with a numeric value taken from an excel table (the final pivot of the score) where the "Alias" has the exact name of a cell in the table? Maybe creating a phpbb table in the sql, if you can tell me how (and how to update it weekly).

In that way, users with an alias corresponding to the player's name in the table would have the score displayed, others won't display anything.

Is that feasible?
I can do all of this manually, but as I said I have many players and I have to do it weekly.
Also nice would be the capability of sorting memberlist by this score...

Can you tell me something?

Thanks in advance!

Lord Phobos
Registered User
Posts: 613
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos » Thu Oct 18, 2018 12:30 pm

The best solution I can think of is to create a table in SQL named phpbb_players. and update it weekly via a csv file converted from excel.
This table would contain the player's name, the number of plays, the number of victories, the victory percentage and finally its score.
Then, I should create a page on my board parsing this table, showing all of this data in nice columns.

Finally, I should have an extension able to make a check between the "Alias" custom field in the user's profile and the player's name in the newly created sql phpbb_players table.
If the values match, then show the value of the score in another custom profile field.
If the values don't match, show "0" or "Unknown", or even better don't show the field at all.

Can someone help me out on all of this as I'm unable to code even this simple stuff?

Thanks a lot!

Lord Phobos
Registered User
Posts: 613
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos » Tue Nov 06, 2018 2:35 am

No one can help me with this project?

I can pay (but fairly, I assume it is quite simple).

User avatar
Restless Rancor
Registered User
Posts: 184
Joined: Tue Sep 18, 2018 1:51 pm

Re: Custom profile field with value from excel

Post by Restless Rancor » Tue Nov 06, 2018 8:21 am

To achieve what you want I think you need to move away from the idea of using CPF's.

I used to use a script to pull data from Google Sheets and display it externally (it was nothing to do with phpBB and was not used on phpBB), but my initial thinking is a script shouldn't be too difficult to write to read the data - then it's a simple case of displaying it cleanly in a table on a new page.

Then, we can add the user ID to the global template variables (if we need to) and use it to lookup the value on the new page. (This would require the spreadsheet data to have the phpBB user ID to identify the member's scores).
From there it'd be a simple task of assigning it to <!-- EVENT viewtopic_body_postrow_custom_fields_before --> in viewtopic_body.html and <!-- EVENT memberlist_view_non_contact_custom_fields_before --> in memberlist_view.html for example.

I'm not too confident in my ability to do this, but am willing to give it a shot starting with manual code edits to the core files, and if I get anywhere then to an extension.

Just a quick note: any solicitation has to be done here. I'm happy to (attempt) this for you for no cost, but being realistic it's going to take me some time to complete (but I should know sooner if it's beyond my knowledge)- it's also assuming you're willing to migrate onto Google Sheets and possibly Google Scripts.

If you have some sample data I could use that would be helpful.

Thanks.
These aren't the droids you're looking for...

Lord Phobos
Registered User
Posts: 613
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos » Thu Dec 06, 2018 2:42 am

I cannot go in depth in explaining why, but I can't move away from the excel file I'm using.
So, I cannot migrate to google sheets.

In this file (totally external to phpbb) I obviously don't have username or userid included, so I thinked about the "alias" workaround.

In short, what I need is a CPF looking in a table named phpbb_players for each user if his "alias" CPF is the same as "alias" in that table.
If a corresponding alias is found, THEN populate another CPF ("Points", or something like that) with the points value.

This is all I need. I also need an advice on the way to populate that table (yes, I am SO ignorant :lol: ).

Last thing, if the memberlist could then be sorted by the "points" number this would be wonderful.

Thanks a lot again.

User avatar
kinerity
Community Team Member
Community Team Member
Posts: 2128
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: Custom profile field with value from excel

Post by kinerity » Thu Dec 06, 2018 12:59 pm

Lord Phobos wrote:
Tue Nov 06, 2018 2:35 am
I can pay (but fairly, I assume it is quite simple).
It's not allowed to offer payment in any form for assistance in this forum. If you feel what you want to do is beyond your capability, then please post in the Wanted! forum where you may find someone willing to take on your project.
Kailey Truscott - Community Team

Lord Phobos
Registered User
Posts: 613
Joined: Tue May 18, 2004 11:41 pm

Re: Custom profile field with value from excel

Post by Lord Phobos » Thu Dec 06, 2018 1:19 pm

I can, not I must.

User avatar
david63
Jr. Extension Validator
Posts: 15761
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: Custom profile field with value from excel

Post by david63 » Thu Dec 06, 2018 3:19 pm

If you are still wanting this as an extension there is a possibility that I might have found a way to do it.

I have just come across PhpSpreadsheet which, in principle, works with phpBB. I have a very basic extension that will load an Excel spreadsheet's data - but that is as far as I have got.

It will take some time to find my way round this class but more so I would need access to a copy of your spreadsheet to ensure that your data is compatible with this class.

And no I am not asking for any payment!
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

Post Reply

Return to “Extension Requests”

Who is online

Users browsing this forum: No registered users and 9 guests