1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Handling Tiered Levels

Discussion in 'Community' started by bespokeappstt, Oct 18, 2021.

Thread Status:
Not open for further replies.
  1. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    Here is a sample of a list called Rentals that I have:
    upload_2021-10-17_21-10-28.png

    The user role that 'Edits' this list has to edit the Serial Number element and the Actual Date In Element at different times and it's causing an issue. The Serial Number will be entered in first, then some time will pass and the Actual Date In has to be entered. The Serial Number is a database join on another list called Serial Assets. Serial Assets have an Available element, a dropdown(yes/no). When Serial Numbers are added to the Rental list, using a php-calc plugin on the Rental list the Availability is toggled to no in the Serial Assets list. When set to no you won't see it and can't add it to this list which is the Rental. This is because in the database join for the Serial Number I have a 'join where specified' as shown below, yes=1 and no=0
    upload_2021-10-17_21-37-19.png

    This works fine when adding new serials to the Rental list as serials cannot be entered twice. However, when its time to add an 'Actual Date In',(later down the road) the Serial Number information is disappears. How can I get the serial number to be permanent while I edit the 'Actual Date In' alone? If I have to do a video on this one I will, its quite a mouthful to explain. If anything requires clarification I will clarify.
     

    Attached Files:

  2. juuser

    juuser Well-Known Member

    Level: Community
    It seems you need to modify the WHERE clause so it would also show currently selected value in addition to available ones. So something like this:

    Code (Text):
    WHERE {thistable}.id IN (SELECT id FROM your-dbjoin-table WHERE id = "{your-current-table___serial-no-field_raw}") OR {thistable}.available = 1
     
    Last edited: Oct 18, 2021
  3. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    This is what I decided to use:
    upload_2021-10-18_10-11-46.png

    So when doing a test I start off with this, as you can see the serial numbers are there but no Actual Date In:
    upload_2021-10-18_10-14-24.png

    When I enter the actual date this is what happens:
    upload_2021-10-18_10-15-48.png

    The serial disappear. Did I enter something wrong in the where clause?
     
  4. juuser

    juuser Well-Known Member

    Level: Community
    Did you read my post?

    Maybe I misunderstood something, but you are using basically the same solution than before, so the results are obviously the same.
    EDIT: I now reliazed you have serial number in a repeat group, so you may need to adjust the Where clause in my post to refer to the repeat table and use parent_id instead of id.
     
  5. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    I am a bit confused. Your post recommended one of two solutions. They were:
    Code (Text):

    WHERE {thistable}.id IN (SELECT id FROM your-dbjoin-table WHERE id = "{your-current-table___serial-no-field_raw}")
     
    or
    Code (Text):

    WHERE {thistable}.available = 1
     
    I chose to implement the second one. How do I refer to the repeat table? Do I use:
    Code (Text):

    where rad_rentals_repeat_serial.parent_id={thistable}.id and {thistable].available=1
     
     
  6. juuser

    juuser Well-Known Member

    Level: Community
    Sorry, I must improve my habit to quote the code properly :). You need to use the whole clause including the "OR" part. So something like:
    Code (Text):
    WHERE {thistable}.id IN (SELECT id FROM your-dbjoin-table WHERE id = "{your-REPEAT_TABLE___serial-no-field_raw}") OR {thistable}.available = 1
    P.S. I'm quite bad at writing code or queries without actually seeing the real site.
     
  7. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    Something really weird happened. I could have sworn it worked the very first time but after that it stopped. I don't think running sql queries on phpmyadmin would affect anything. In the end this is not working and I decided to do a silent move on it.
    https://pbsrasta.s3.us-east-2.amazonaws.com/fabrikIssue.mp4
     
  8. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    This is what I have in the where clause:
    upload_2021-10-18_15-55-0.png
     
  9. juuser

    juuser Well-Known Member

    Level: Community
    Running SQL-queries in PhpMyAdmin can most certainly affect a lot depending on the queries you run of course.

    Just noticed you have a SQL-syntax error in list header flashing for a second in the video. These kind of thing are the first to focus on.

    If you remove the WHERE clause FROM serial element does it work then (no error message and the selected serial numbers get saved correctly)?

    If the SQL error remains after removing the WHERE clause, you need to look elsewhere. If you have some other calc elements, disable them and see if the error disappears etc.
     
  10. troester

    troester Well-Known Member Staff Member

    Level: Community
    Your video is showing a multi-select serial number (is it a dbjoin multi or a dropdown multi?), you didn't mention this before.
    id="{...serial_raw}" won't do in this case, you'll need some id IN.
    I can't see a quick solution how to get the serialnumber array into there, maybe with one more subquery running against the parent_id or an additional calc element...)
     
    juuser likes this.
  11. juuser

    juuser Well-Known Member

    Level: Community
    @troester, good point as always. {rad_rentals_repeat_serial___serial_raw} is an array in that case and won't work in WHERE clause. If it's a databasejoin element rendered as multiselect, then something like that should do:

    Code (Text):
    WHERE {thistable}.id IN (SELECT a.id FROM rad_serial_assets AS a
    LEFT JOIN rad_rentals_repeat_serials AS b ON a.id = b.serial
    WHERE b.parent_id = "{your-table-name-where-the-current-element-is___id_raw}") OR {thistable}.available = 1
     
  12. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    My apologies, yes it's a multi-select dropdown box:
    upload_2021-10-19_10-7-45.png

    I'll try the new query.
     
  13. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    Last edited: Oct 19, 2021
Thread Status:
Not open for further replies.

Share This Page