Problem with Element Dropdown, same values different labels

[marioms]

Hello there!

I am having trouble making this work. So I have a billing system that calculates a total amount of money based on this:

And a few of the contractors have the same rate per hour:

This is how it is being displayed on the form:

And once I submit, I can see the newly created entry on the list but the contractor is wrong, it just selects a random contractor with the same value:

Is there a way to make it show the correct contractor? (i.e the one I selected on the form, even if they have the same values?) if not, what kind of solution I can apply?

Also, when I am back to the form for edit, it shows a totally different contractor again (but one with the same value as the selected one). So I am not sure how to fix this issue.

Thank you in advance!

 

[troester]

You can't have the same value for different labels. The value is stored in the DB, how should anybody know which of the different labels belongs to it?
You can use an additional table/list with e.g.
id, rate, contractor
and a databasejoin element pointing to this table (with value=id[recommended]).

You can then fetch the rate from the database.

 

[juuser]

You've got the "value" concept totally wrong. If you use dropdown element, "value" should be unique like "contractor_name" or numeric like 1, 2, 3 etc.

And even better to use @troester's suggestion to have a separate list and dbjoin element for contractors.

 

[marioms]

You can't have the same value for different labels. The value is stored in the DB, how should anybody know which of the different labels belongs to it?
You can use an additional table/list with e.g.
id, rate, contractor
and a databasejoin element pointing to this table (with value=id[recommended]).

You can then fetch the rate from the database.

Thank you so much, that makes sense, I will make the additional table and the databasejoin element

 

[marioms]

You've got the "value" concept totally wrong. If you use dropdown element, "value" should be unique like "contractor_name" or numeric like 1, 2, 3 etc.

And even better to use @troester's suggestion to have a separate list and dbjoin element for contractors.

You are right, I totally got it wrong, I guess a need a coffee because my brain is not working properly this morning

 

[marioms]

Good morning!

I'm back again. I have done the new table with the ID, Contractor name and rate:

And I created the new databasejoin element:

I also updated the formula I have for calculating the rate * hours:

So it is obviously not working at the moment because the new element points at the id of the contractor (with the name as a label) and not at the rate to make the calculation:

So I would like to know how to fetch the rate so the calculation works. Is that something I need to add to the databasejoin element itself? to the formula?

Thank you in advance.

 

[juuser]

In calc element you can get the hourly rate something like:

$contractor = '{j32db_scitech___Contractor_raw}';

$mydb = JFactory::getDBO();
$mydb->setQuery("SELECT rate FROM j32db_contractors WHERE id = ".$mydb->Quote($contractor));
$hourlyrate = $mydb->loadResult();

Make sure you change the "rate" in the query to your real rate element name if it's something different.

 

[marioms]

In calc element you can get the hourly rate something like:

$contractor = '{j32db_scitech___Contractor_raw}';

$mydb = JFactory::getDBO();
$mydb->setQuery("SELECT rate FROM j32db_contractors WHERE id = ".$mydb->Quote($contractor));
$hourlyrate = $mydb->loadResult();

Make sure you change the "rate" in the query to your real rate element name if it's something different.

Thank you so much Juuser, it works fine!